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

ReWorkflow has a query that can find queries (and associated recipient lists) referencing a mailing: 86439254-d649-48c6-842e-87407dddd850:rwf

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.

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''%'

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

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'

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 + '%'
		)

Rule Conditions

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''%'

Rule "From Formula" Actions

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 ''.

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%'

Mailings

Mailings don't reference fields directly, so look for recipient queries that reference the target field. The query in the Suitcase at the top of this article can help!

Find Prompt Conditions

TODO 🙃

Find Field Conditions

TODO 🙃

Find Reports

Yet another TODO 🙃