Useful MSSQL queries for additional fields
Since i was not able to find the queries i needed i made my own and decided to share them in case they could be useful for someone else.
Tested on build 14.9 Build 14920
MSSQL database
Show additional fields and what templates (if any) they are used in:
- SELECT
- ca.aliasname AS "Field Alias",
- fc.form_name AS "Form Name",
- fc.field_name AS "Field Name",
- rtl.templatename AS "Template Name"
- FROM
- form_customizer fc
- INNER JOIN
- columnaliases ca ON CAST(ca.columnaliasesid AS VARCHAR) = SUBSTRING(fc.field_name, CHARINDEX('_', fc.field_name, CHARINDEX('_', fc.field_name)+1)+1 , LEN(fc.field_name))
- LEFT JOIN
- RequestTemplate_List rtl ON rtl.templatename = SUBSTRING(fc.form_name, CHARINDEX('_', fc.form_name)+1 , LEN(fc.form_name));
This will make a list of all request additional fields and show what templates they are used in.
Look up a specific additional field to see where it is used:
- SELECT
- ca.aliasname AS "Field Alias",
- fc.form_name AS "Form Name",
- fc.field_name AS "Field Name",
- rtl.templatename AS "Template Name"
- FROM
- form_customizer fc
- INNER JOIN
- columnaliases ca ON CAST(ca.columnaliasesid AS VARCHAR) = SUBSTRING(fc.field_name, CHARINDEX('_', fc.field_name, CHARINDEX('_', fc.field_name)+1)+1 , LEN(fc.field_name))
- LEFT JOIN
- RequestTemplate_List rtl ON rtl.templatename = SUBSTRING(fc.form_name, CHARINDEX('_', fc.form_name)+1 , LEN(fc.form_name))
- WHERE
- LOWER(ca.aliasname) LIKE '{UDF FIELD NAME}';
Replace {UDF FIELD NAME}' with the name of the additional field you want to get listed. It will show what templates are currently using the additional field and also show if it is not in use.