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.

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:

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 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 🙃