Created On:  25 February 2011

Problem:

How do you query a StarTeam database to return information on deleted files?

Resolution:

The following query will return details of files deleted in StarTeam. Information includes:
  • File name
  • Dot notation
  • Name of user who deleted file
  • Time of deletion

SELECT

  syn_user.full_name [Deleted By User Name],
  syn_project.name [Project Name],
  syn_view.name [view],
  syn_file.name [File Name],
  syn_file.dotnotation [Dot Notation],
  CAST('01/01/1900' AS DATETIME) syn_viewmember.deletedtime [Deleted Date]
FROM
  syn_viewmember
  INNER JOIN syn_user ON syn_user.id = syn_viewmember.deleteduserid
  INNER JOIN syn_file ON syn_viewmember.id = syn_file.id and syn_viewmember.classid = (select id from ctables where stname = 'File')
  INNER JOIN syn_view ON syn_view.id = syn_file.viewid
  INNER JOIN syn_project ON syn_project.id = syn_file.projectid
--
-- optional clause - if you require *only* file information deleted by a specific user, include the following 'where' clause. For example,
--
-- WHERE syn_user.full_name LIKE '�ministrator%'
--
ORDER BY [Deleted Date] DESC