Finding Field Usage

The Fields Search tool only goes so far. Here’s some helpful SQL that can help directly search the database to find field usage.

Jasmine Solomon at NYU packaged some of these techniques up into a convenient CJ suitcase: cfa87977-e1b5-4d92-a522-b730c62fe751:nyu

Some of these queries include the filters qp.[active] = 1 AND q.[archived] = 0. These exclude query parts and entire queries that are inactive/archived. Depending on your purpose you may wish to remove these filters.

Find all queries referencing a particular text string

This is a general, brute-force method. It will produce some false positives, such as a filter named “Brace Yourself.”

SELECT *
FROM [query]
WHERE [sql] LIKE '%race%'

By looking for 'race' in single quotes only, you can get slightly narrower results.

SELECT *
FROM [query]
WHERE [sql] LIKE '%''race''%'

Find formulas and other customizable query parts

This works for both CJ’s and local bases, but it will not return standard CJ exports. It will return most CJ formulas.

SELECT qp.*
FROM [query.part] qp
INNER JOIN [query] q
    ON q.[id] = qp.[query]
WHERE 1 = 1
    AND qp.[active] = 1
    AND q.[archived] = 0
    AND qp.[sql] LIKE '%''race''%'s

Find query Export parts (CJ)

Standard CJ exports and filters precisely record the field they reference. We can join all the way back to the [lookup.field] table. This example returns all CJ queries utilizing a particular field:

SELECT *
FROM [query.part] qp
INNER JOIN [lookup.query] lq
    ON lq.[id] = qp.[source]
INNER JOIN [lookup.field] lf
    ON lf.[uuid] = lq.[source]
WHERE lf.[id] = 'race'

Alternate method that only works for Exports:

SELECT qp.*
FROM [query.part] qp
INNER JOIN [query] q
    ON q.[id] = qp.[query]
WHERE 1 = 1
    AND qp.[active] = 1
    AND q.[archived] = 0
    AND qp.[field] = 'race'

Find query Exports/Filters (legacy tool)

This can also find other strings, not just field ID's. This example returns a list of Exports/Filters (in the Database tool) that reference a field. The "Export/Filter References (legacy tool)" can then be used to find queries that use the custom exports/filters.

DECLARE @my_field VARCHAR(32) = 'my_field'

SELECT *
FROM [lookup.query] lq    
WHERE [base] IN (
		SELECT [id]
		FROM [lookup.base]
		WHERE [source_type] IS NULL
		)
	AND [source_type] IS NULL
	AND (
		[select] LIKE '%' + @my_field + '%'
		OR [join] LIKE '%' + @my_field + '%'
		OR [where] LIKE '%' + @my_field + '%'
		OR [whereselect] LIKE '%' + @my_field + '%'
		)

Find rules referencing a field

The Field Search Tool returns rules that set a field, but not rules that reference a field in the criteria. Here's quick and dirty method to find all references:

SELECT *
FROM [rule]
WHERE cast([xml] AS VARCHAR(max)) LIKE '%''my_field''%'

Find rules with "from formula" actions that include ‘my_field’ Export Value

Here's an example syntax to search the XML more narrowly:

SELECT *
FROM [rule]
WHERE cast([xml] AS VARCHAR(max)) LIKE '%[[]export] from [[]field]%[[]field] = ''my_field'')%'

The [ character needs to be escaped like [[] when used in the LIKE operator. ] doesn't need escaped.
Similarly, ' needs to be escaped like ''.

Find Form Merge Fields

A crude, wide net:

SELECT *
FROM [form]
WHERE 1 = 1
	AND try_cast([config] AS NVARCHAR(max)) LIKE '%my_field%'

A narrower approach; you may need to try several search terms (field ID, export name) to find what you're looking for.

SELECT *
FROM [form]
WHERE 1 = 1
	AND try_cast([config].query('//p[k/text()="field_merge"]/v/*') AS NVARCHAR(max)) LIKE '%field_id%'

Find Prompt Conditions

TODO 🙃

Find Field Conditions

TODO 🙃

Find Reports

Yet another TODO 🙃