Created On:  30 March 2012

Problem:

SQL Server query to return details of Custom Enumerated fields added to StarTeam configuration

Resolution:

For best results when running in a SQL Query window:

1.  Open 'Tools | Options'

2.  Expand 'Query Results | SQL Server'

3.  Select 'Results to text' for the default destination

Open a new query window for this to take effect

SET NOCOUNT ON
DECLARE @cf_fid varchar(10)
DECLARE @cf_name varchar(30)
DECLARE @cf_objstr varchar(1000)
DECLARE @disp varchar(100)
DECLARE @enum varchar(100)
DECLARE @stcode varchar(10)

DECLARE cf CURSOR FOR
SELECT
STFIELD, STNAME, OBJSTID FROM CFIELDS
WHERE
STTABLEID = (SELECT ID from CTABLES WHERE STNAME = 'Change') AND
STNAME LIKE 'Usr_%' AND
STFLDTYPE=2 -- enumerated types ONLY
ORDER BY STNAME

OPEN cf
FETCH NEXT FROM cf INTO @cf_fid, @cf_name, @cf_objstr
WHILE (@@fetch_status <> -1)
BEGIN
DECLARE c_str CURSOR FOR
SELECT STSTRING FROM CTSTRC WHERE STRID = @cf_objstr

OPEN c_str
FETCH NEXT FROM c_str INTO @disp
SELECT 'ST ID ', 'ST Name ', 'Display Name'
WHILE (@@fetch_status <> -1)
BEGIN
SELECT @cf_fid 'ST ID', @cf_name 'ST Name', @disp 'Display Name'
DECLARE c_vals CURSOR FOR
SELECT ce.stcode, cs.ststring FROM ctstrc cs, cenums ce WHERE cs.strid like('IDS_ENUM_Change_' @cf_fid '%''IDS_ENUM_Change_' @cf_fid '%') AND cs.strid = ce.enumstid
OPEN c_vals
FETCH NEXT FROM c_vals INTO @stcode, @enum
SELECT ' ','Enum Code ', 'Enum Value'
WHILE (@@fetch_status <> -1)
BEGIN
SELECT ' ', @stcode 'Enum Code', @enum 'Enum Value'
FETCH NEXT FROM c_vals INTO @stcode, @enum
END
DEALLOCATE c_vals
FETCH NEXT FROM c_str INTO @disp
END

SELECT '----------------------------------------------------------'
DEALLOCATE c_str
FETCH NEXT FROM cf INTO @cf_fid, @cf_name, @cf_objstr
END
DEALLOCATE cf

Example Results (as output with ‘Results to Text’):

n/a