Earlier today, a developer came by my desk wishing to know who had a number of files checked out which were part of a rather large project he had just completed.
A real quick glance from the Team Foundation Services Source Control explorer told me that, in fact, no one had those files checked out... yet Visual Studio was showing that they were.
Apparently when a file is a part of a shelveset, Visual Studio displays the icon identifying the file is checked out to another user. The developer was concerned that all his changes might be accidentally overwritten when the shelveset was unshelved and wanted to know, for curiosity sake, who had shelved changes for those files.
Long story short... I was actually the person who had the files in a shelveset (it's amazing how sleep can make you forget what you did just the day before), but finding that information out was not a straight-forward task... and I wasn't about to attempt an "Unshelve" for every shelveset, for every developer in our company.
I ended up writing the stored procedure below (for the TfsVersionControl database) which produces a list of files for a given shelveset. It allows me to query any shelveset by shelvesetName, ownerID, or filePath; these parameters are mutually exclusive and the procedure is written to handle any combination. The shelvesetName is a direct comparison (not case sensitive) while the filePath is a wildcard search on both ends of the path.
1: CREATE PROCEDURE sp_QueryShelvesetContents (
2: @ownerID as int = NULL,
3: @shelvesetName as nvarchar(64) = NULL,
4: @filePath as nvarchar(520) = NULL
5: )
6: AS
7: SET NOCOUNT ON
8:
9: BEGIN
10: --Prep the filePath for a LIKE statement
11: IF @filePath IS NULL SET @filePath = '%$%'
12: SET @filePath = LTrim(RTrim(@filePath))
13: IF LEFT(@filePath, 1) <> '%' SET @filePath = '%' + @filePath
14: IF RIGHT(@filePath, 1) <> '%' SET @filePath = @filePath + '%'
15:
16: --Create a temp table to hold workspace IDs
17: CREATE TABLE #tmpWorkspaces (WorkspaceID int, OwnerID int)
18:
19: IF @shelvesetName IS NOT NULL
20: BEGIN
21: --Add all shelvesets which match the shelveset name
22: INSERT INTO #tmpWorkspaces
23: SELECT WorkspaceID, OwnerID FROM dbo.tbl_Workspace WHERE WorkspaceName = @shelvesetName AND Type = 1
24: END
25:
26: IF @ownerID IS NOT NULL
27: BEGIN
28: --Remove any shelvesets which don't match the owner passed in (even if it matched the workspace name)
29: DELETE FROM #tmpWorkspaces WHERE OwnerID <> @ownerID
30:
31: IF @shelvesetName IS NULL
32: BEGIN
33: --Add all shelvesets which match the ownerid (and aren't already in the table)
34: INSERT INTO #tmpWorkspaces
35: SELECT W.WorkspaceID, W.OwnerID
36: FROM dbo.tbl_Workspace AS W
37: LEFT JOIN #tmpWorkspaces AS TW ON TW.WorkspaceID = W.WorkspaceID
38: WHERE W.OwnerID = @ownerID AND TW.WorkspaceID IS NULL
39: END
40: END
41:
42: IF (SELECT COUNT(WorkspaceID) FROM #tmpWorkspaces) > 0
43: BEGIN
44: SELECT WS.WorkspaceID, WS.WorkspaceName AS ShelvesetName, I.IdentityID, I.DisplayName, PC.TargetServerItem
45: FROM dbo.tbl_PendingChange AS PC
46: INNER JOIN #tmpWorkspaces AS TW ON TW.WorkspaceID = PC.WorkspaceID
47: INNER JOIN dbo.tbl_Workspace AS WS ON WS.WorkspaceID = TW.WorkspaceID
48: INNER JOIN dbo.tbl_Identity AS I ON I.IdentityID = WS.OwnerID
49: WHERE TargetServerItem LIKE @filePath
50: END
51: ELSE
52: BEGIN
53: SELECT WS.WorkspaceID, WS.WorkspaceName AS ShelvesetName, I.IdentityID, I.DisplayName, PC.TargetServerItem
54: FROM dbo.tbl_PendingChange AS PC
55: INNER JOIN dbo.tbl_Workspace AS WS ON WS.WorkspaceID = PC.WorkspaceID AND WS.Type = 1
56: INNER JOIN dbo.tbl_Identity AS I ON I.IdentityID = WS.OwnerID
57: WHERE TargetServerItem LIKE @filePath
58: END
59: END
60:
61: DROP TABLE #tmpWorkspaces
Example 1: List of all files in your DEV branch which are part of any shelveset
Example 2: List of all files in your DEV branch which are part of a shelveset for ownerID 5