SQL Server script to return details of StarTeam Folders and their File contents (DATAMART tables)

[AndrewT_MicroFocus]

The ST_VIEWMEMBER table is in essence a collection of every item that is extracted to Datamart, which in turn links to the relevant tables (ST_FOLDERS, ST_FILES etc).
This relationship is not immediately obvious.

There are three columns that will be of interest.
Firstly 'ID' - this is directly linking to the ST_FILES/ST_FOLDERS table(s) depending of course on the 'TypeName' column.
The other columns - 'ItemID' and 'ParentFolderID' do not link outside the ST_VIEWMEMBER table; For example, if 'ParentFolderID' = -1 it is a "Root" Object, otherwise 'ParentFolderID' will reference back to 'ItemID'.
These two ID fields are unique to the ST_VIEWMEMBER table, they do not reference any IDs outside this table.

An example that returns 'Folder' information;

declare @proj VARCHAR(50)
declare @view VARCHAR(50)
set @proj = 'PROJECT NAME'
set @view = 'VIEW NAME'

-- query 1: returns the Folder (ST_FOLDERS) information for the above Project & View from the ST_VIEWMEMBER table
select * from st_viewmember where
TypeName = 'Folder' and
ProjectID = (select ID from ST_PROJECTS where name = @proj) and
ViewID = (select ID from ST_VIEWS where Name = @view) and
id in (
select id from ST_FOLDERS
where CreateProjectID = (select ID from ST_PROJECTS where name = @proj) and viewid = (select ID from ST_VIEWS where Name = @view)
)
order by FolderHierarchy

And a second query that returns 'File' information;

declare @proj VARCHAR(50)
declare @view VARCHAR(50)
set @proj = 'PROJECT NAME'
set @view = 'VIEW NAME'

-- query 2: same as above, but for files (ST_FILES)
select * from st_viewmember where
TypeName = 'File' and
ProjectID = (select ID from ST_PROJECTS where name = @proj) and
ViewID = (select ID from ST_VIEWS where Name = @view) and
id in (
select id from ST_FILES
where ProjectID = (select ID from ST_PROJECTS where name = @proj) and viewid = (select ID from ST_VIEWS where Name = @view)
)
order by FolderHierarchy

Now, to combine the two queries to return Folder information PLUS File information, the only feasible method due to the complexity of the ST_VIEWMEMBER table, would be to use a SQL Cursor to iterate through ST_VIEWMEMBER and for each piece of Folder information and use this to query the corresponding File information.
Basically because you are making circular references to the same table, this is probably the most efficient method.

The following query (using a cursor to identify folders) returns (for each folder name) the count of files in that folder - for the specified Project and View;

DECLARE @proj VARCHAR(50)
DECLARE @view VARCHAR(50)
DECLARE @folderid int, @vmfolderid int
DECLARE @fileid int, @vmfileid int
SET @proj = 'PROJECT NAME'
SET @view = 'VIEW NAME'

DECLARE getfolders CURSOR FOR 
  SELECT ID, ItemID FROM ST_VIEWMEMBER 
  WHERE 
    TypeName = 'Folder' AND 
    ProjectID = (SELECT ID FROM ST_PROJECTS WHERE Name = @proj) AND
    ViewID = (SELECT ID FROM ST_VIEWS WHERE Name = @view) AND
    ID IN (
      SELECT ID FROM ST_FOLDERS
      WHERE 
        CreateProjectID = (SELECT ID FROM ST_PROJECTS WHERE Name = @proj) AND 
        ViewID = (SELECT ID FROM ST_VIEWS WHERE Name = @view)
    )
  ORDER BY FolderHierarchy

  OPEN getfolders
  FETCH NEXT FROM getfolders into @folderid, @vmfolderid
  while (@@fetch_status <> -1)
  BEGIN
    SELECT @proj [Project], @view [View], Name [Folder Name] FROM ST_FOLDERS WHERE ID = @folderid
    SELECT count(id) [File Count] FROM ST_VIEWMEMBER 
    WHERE 
      TypeName = 'File' AND
      ProjectID = (SELECT ID FROM ST_PROJECTS WHERE Name = @proj) AND
      ViewID = (SELECT ID FROM ST_VIEWS WHERE Name = @view) AND
      ID IN (
        SELECT ID FROM ST_FILES
        WHERE
          ProjectID = (SELECT ID FROM ST_PROJECTS WHERE Name = @proj) AND 
          ViewID = (SELECT ID FROM ST_VIEWS WHERE Name = @view)
      ) AND
      ParentFolderID = @vmfolderid
    FETCH NEXT FROM getfolders INTO @folderid, @vmfolderid
  END
DEALLOCATE getfolders

/* Note: ID is the folder id that ties to ST_FOLDERS;
ItemID and ParentFolderID refer specifically to the ST_VIEWMEMBER table ONLY
*/