Detecting if a file is a merge in TFS VersionControl database

I was trying to run some metric calculations on files within a changeset, but I only wanted new files – i.e. I wanted to filter out merged, branched, or renamed files. For example, if someone created a branch, that shouldn’t count as adding 1000 new files.

One solution I found was to check the Command column of the TfsVersionControl.dbo.tbl_Version table. I realize the TfsVersionControl is a transactional database, and reports are encouraged to go off of TfsWareHouse, but that didn’t seem to contain this field.

Here’s the relevant SQL (NOTE: this is for VS2008, I haven’t tested it on VS2010).

select 

      CS.ChangeSetId, FullPath, Command, CreationDate,

      case

            when Command in (2,5,6,7,10,34) then cast(1 as bit)

        else cast(0 as bit)

      end as IsNew

from TfsVersionControl..tbl_Version V with (nolock)

      inner join TfsVersionControl..tbl_ChangeSet CS with (nolock)

      on V.VersionFrom = CS.ChangeSetId

where CS.ChangeSetId = 20123

The question becomes, what does the “tbl_Version .Command” column mean, and why those specific values? I couldn’t find official documentation (probably because it’s discouraged to run queries on it), so I did a distinct search on 50,000 different changesets to find all values being used, and I worked backwards comparing it against the Team Explorer UI to conclude it appears to be a bitflag for actions:

Command	Bit value
add	1
edit	2
branch	4
rename	8
delete	16
undelete	32
branch	64
merge	128

Recall there can be multiple actions (hence the bit field), such as merge and edit. So, if you want to find new code – i.e. adds or edits, then we’d take the following bit flags: 2, 5, 6, 7, 10, and 34.

Is New?	Bit value	Actions
Yes	2	edit
Yes	5	add (folder)
Yes	6	type/edit
Yes	7	add (add file)
No	8	rename
Yes	10	rename/edit
No	16	delete
No	24	delete,rename
No	32	undelete
Yes	34	undelete, edit
No	68	branch
No	70	branch, edit
No	84	branch,delete
No	128	merge
No	130	merge, edit
No	136	merge,rename
No	138	merge,rename,edit
No	144	merge,delete
No	152	merge, delete, rename
No	160	merge, undelete
No	162	merge, undelete, edit
No	196	merge, branch
No	198	merge, branch, edit
No	212	merge, branch, delete

Of course, this is induction, and it’s possible I may have missed something, but given a large sampling and lots of spot-checking, it appears to be reliable.