Highlighted
Micro Focus Expert
Micro Focus Expert
188 views

SQL Server script to return details of Custom Fields/Attributes added to StarTeam Configuration

----
-- For best results (when running in SQL Query window) open 'Tools | Options' and
-- expand 'Query Results | SQL Server' and select 'Results to text' for the default destination
-- (you will need to 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 stfieldid, stname, objstid FROM cfields
  WHERE
    STTABLEID = (select ID from CTABLES WHERE STNAME = 'Change') AND -- CRs ONLY; change ALL references to this item if required
    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+'%') 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

Tags (3)
0 Likes
0 Replies
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.