Find UDFs in the TMS Database
This topic is for TMS system administrators or other users who are familiar with working directly in the TMS database and with working with SQL code.
It can be difficult to determine what the UDF labels are when working in the TMS database. Below is a SQL query that will output the Section, Segment, dependent field values, and the label for UDFs. Run this query on your TMS database:
SELECT s.SectionName, sg.Name as SegmentName,
CASE WHEN IDFieldDependsOn = 3 THEN 'Segment'
WHEN IDFieldDependsOn = 5 THEN 'Type'
WHEN IDFieldDependsOn = 57 THEN 'Category'
WHEN IDFieldDependsOn = 58 THEN 'Subcategory' END as [Depends on Field],
CASE WHEN IDFieldDependsOn = 3 THEN (Select max(Name) FROM tblSegments WHERE IDSegment = IDValueDependsON GROUP BY IDSegment)
WHEN IDFieldDependsOn = 5 THEN (Select max(code) FROM tblTypeCodes WHERE IDType = IDValueDependsON GROUP BY IDType)
WHEN IDFieldDependsOn = 57 THEN (Select max(code) FROM tblCategoryCodes where IDCategory = IDValueDependsON GROUP BY IDCategory)
WHEN IDFieldDependsOn = 58 THEN
(SELECT max((c.code + ' | ' + s.code)) FROM tblSubcategorycodes s
INNER JOIN tblCategoryCodes c on s.IDCategory = c.IDCategory
WHERE s.IDSubCategory = IDValueDependsON GROUP BY s.IDSubCategory)
END as [Depends on Value],
'UDF' + cast((IDField - 39) as varchar(5)) as [UDF Field],
FieldXml.value('data(/Field/@LabelText)[1]','nvarchar(200)') as [UDF Label]
FROM tblTMSUserDefinedFieldProperties udf INNER JOIN tblTMSSections s on udf.IDSection = s.idSection
INNER JOIN tblSegments sg on udf.IDSegment = sg.IDSegment
ORDER BY udf.IDSection, udf.IDSegment, IDField, IDFieldDependsOn