TFS 2013

TFS command line 

https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2008/z51z7zy0(v=vs.90)?redirectedfrom=MSDN

https://stackoverflow.com/questions/3762812/scripting-tfs-command-line-for-get-latest-version-check-out-and-check-in-progr

https://programmaticponderings.com/tag/microsoft-teamfoundation-powershell/

https://www.donovanbrown.com/post/PowerShell-I-would-like-you-to-meet-TFS-and-VSTS

WorkItemLatest : contains work items created

    Id - > contains the id of the work item
    title --> Contains title of the work item
   work item type --> contains the tpe of the work item like Task, Bug


tbl_Version --> 

                     File and its version is marked here 

                     Itemtype column has 1 for folder and 2 for file

                     command  column has file and folder change type below are identified file changes 

                            Filechanges 

                                    2  -- Edit
                                   7  -- Add
                                 16  -- Delete
                               514  -- Reverted from the pervious version like we have v1, v2 , v3 and now v1 

                                              and v3 are same files
                               528  -- mistakely Added  a file it was deleted
                              544  -- deleted and Re-added
                            1040  --- rename of the file, which means deletion of the old file and adding new file
                            2112  --- Add of the file
                            2114  --- Add  

tbl_Changeset
tbl_workspace

tbl_project --> Projects present in the collection

--[dbo].[LinksLatest] -- links for source and target are mentioned here
--[dbo].[LinksAre] -- links for source and target are mentioned here from linksare have more details like who created it and all when compared to linkslatest
--[dbo].[LinkTypes] -- Links types master table
--[dbo].[LinksWere]  -- History Link details are mentioned here

--[dbo].[WorkItemTypeUsages]
--[dbo].[WorkItemTypesRenamed]
--[dbo].[WorkItemTypes]
--[dbo].[WorkItemTypeCategoryMembers]
--[dbo].[WorkItemTypeCategories]
--[dbo].[WorkItemsWere] --history of the work item status flow
--[dbo].[WorkItemsLatest] -- Latest of work item
--[dbo].[WorkItemsDestroyed]
--[dbo].[WorkItemsAre] --workitem master table but not sure about difference between workitemsare and WorkItemsLatest


To Know the User name who summitted the changeset use the below query

select chng.ChangeSetId,chng.CreationDate, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CreationDate) AS CheckinDate,chng.Comment,cnst.NamePart,cnst.DisplayPart,cnst.String from tbl_ChangeSet chng, Constants cnst, tbl_VCIdentityMap idn
where chng.PartitionId=idn.PartitionId and chng.OwnerId=idn.IdentityId and
idn.TeamFoundationId=cnst.TeamFoundationId

Note: The Datetime field is in UTC so we need to massage it.
           Currently I was unable to identify the difference between OwnerId and CommitterId


To know the top 100 changes done 

SELECT TOP 100

V.ChildItem AS [File name],

CS.Comment,

--I.DisplayName AS [Changed By],

cnst.NamePart,cnst.DisplayPart,cnst.String,

CS.CreationDate AS [Date],DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CS.CreationDate) AS CheckinDate

FROM tbl_Changeset CS

INNER JOIN tfs_NVKIDS.[dbo].tbl_VCIdentityMap I ON I.IdentityID = CS.OwnerID

inner join Constants cnst on i.TeamFoundationId=cnst.TeamFoundationId

INNER JOIN tbl_Version V ON V.VersionFrom = CS.ChangesetID

ORDER BY CS.CreationDate DESC

To have TFS plugin for eclipse

https://www.softwaretestinghelp.com/tfs-for-java-eclipse-devops/

To download the plugin in eclipse we need us he url http://dl.microsoft.com/eclipse/tfs

TFS and Powershell to get updated filelist

https://programmaticponderings.com/2012/08/10/using-powershell-to-generate-a-tfs-change-manifest-for-artifact-delivery/

The Query will help to identify the file is associated to which the item the file name should be unique

select tblv.ParentPath,tblv.ChildItem,  versionFrom,wif.id,wil.Title,wil.[Work Item Type] from tbl_version tblv , WorkItemFiles wif,WorkItemsLatest wil where
 tblv.versionTo=2147483647  -- latest files
 and tblv.ItemType=2 -- to fetch files only then we need to give 2
 and tblv.ParentPath like '$\Service\service\%'
 and tblv.childItem in ('CreateAndMaintainCaseServiceImpl.java\' , 'ParticipantAssetsService.java\' , 'ParticipantAssetsServiceImpl.java\' , 'ParticipantDemographicsService.java\' ,'ParticipantDemographicsTransactionService.java\' , 'ParticipantDemographicsTransactionServiceImpl.java\' , 'PendingReferralsServiceImpl.java\' ,'SearchforCasesServiceImpl.java\')
 and wif.FilePath like 'vstfs:///VersionControl/Changeset/%'
 and tblv.VersionFrom = CASE WHEN wif.FilePath like 'vstfs:///VersionControl/Changeset/%'
          THEN CAST(Replace(wif.FilePath, 'vstfs:///VersionControl/Changeset/','') as int)
          ELSE CAST('' as int)
         END
 and wil.id = wif.id


To know changes done on a particular day 

select  Replace ( Substring ( REPLACE (Replace (FullPath, '>', '_'),'"','-'),  1, Len (FullPath) - 1), '\', '/')  full_Path,

       Replace ( Substring ( REPLACE (Replace (ParentPath, '>', '_'),'"','-'),  1, Len (ParentPath) - 1), '\', '/')  parent_Path,

   Replace ( Substring ( REPLACE (Replace (ChildItem, '>', '_'),'"','-'),  1, Len (ChildItem) - 1), '\', '/')  child_Item,

   tblv.VersionFrom,tblv.Command,

                      CASE

                         WHEN tblv.Command = 2 THEN 'Edit'

                         WHEN tblv.Command = 5 THEN 'Folder Add'

                         When tblv.Command IN (2112,2114,7) THEN 'Add'

WHEN tblv.Command IN (16,1040) THEN 'Delete'

WHEN tblv.Command =3136 THEN 'Rename'

                         ELSE ''

                      END

                       ChangeType from tbl_version tblv join tbl_Changeset tblc

on tblc.ChangeSetId=tblv.VersionFrom

where tblv.fullPath like '$\NVWorkItem\Documents\1.Development\Unit test reports\%'

and tblv.VersionTo=2147483647

and tblc.CreationDate between '11/28/2020' and '11/30/2020'