Created On:  25 February 2011

Problem:

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

Resolution:

The following query will return details of files currently 'locked' in StarTeam. Information includes:
  • File name
  • Project & View names,
  • User(s) locked by,
  • Type of lock (exclusive, non-exclusive, etc.)

SELECT

  syn_project.name [Project Name],
  syn_view.name [view],
  syn_file.name [File Name],
  syn_user
.full_name [Locked By User Name],
  [Exclusive Lock] = case syn_itemlock.ExclusiveLock
    when '0' then 'Non-exclusive'
    when '1' then 'Exclusive'
    else 'Other'
  END

FROM
syn_itemlock
  INNER JOIN syn_viewmember on syn_viewmember.id = syn_itemlock.itemvmid
  INNER JOIN syn_file ON syn_file.rootobjectid = syn_viewmember.rootobjectid
  INNER JOIN syn_user ON syn_user.id = syn_itemlock.userid
  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 [Project Name], [view]