Get the latest changeset ID of all the project in TFS

Get the latest changeset ID of all the project in TFS using SQL query

USE Tfs_FabrikamFiberCollection

CREATE TABLE #TB1_PROJECTNAMES (cdate datetime,cid int,fpath nchar(1000))

CREATE TABLE #TB1_ACTIVEPROJECTNAMES (ID INT IDENTITY(1,1),ChangedDate datetime,ChangesetId int,ProjectName nchar(1000))

insert into #TB1_PROJECTNAMES (cdate,cid,fpath)

SELECT

    chg_set.CreationDate,

    chg_set.ChangeSetId,

    v.FullPath

FROM dbo.tbl_ChangeSet (nolock)AS chg_set

    INNER JOIN dbo.tbl_Version (nolock)AS v ON chg_set.ChangeSetId = v.VersionFrom

    LEFT OUTER JOIN dbo.tbl_File (nolock) AS f ON v.FileId = f.FileId

WHERE (chg_set.CreationDate > '2013-05-01')

ORDER BY chg_set.CreationDate, v.FullPath

UPDATE #TB1_PROJECTNAMES

SET fpath = left(fpath, charindex('\', fpath, charindex('\', fpath)+1)-1)

UPDATE #TB1_PROJECTNAMES

SET fpath = REPLACE (fpath, '$\', '')

insert into #TB1_ACTIVEPROJECTNAMES (ChangedDate,ChangesetId,ProjectName)

select a.* from #TB1_PROJECTNAMES a

inner join

(

 select distinct fpath, max(cid) as cid from #TB1_PROJECTNAMES

  group by fpath

) as b

on a.fpath = b.fpath and a.cid = b.cid

UPDATE #TB1_ACTIVEPROJECTNAMES

SET ProjectName = RTRIM(LTRIM(ProjectName))

select * from #TB1_ACTIVEPROJECTNAMES

DROP TABLE #TB1_PROJECTNAMES

DROP TABLE #TB1_ACTIVEPROJECTNAMES