Finding Query Exports and Filters by Name

Need to find all queries that use a particular filter or export? This will search for query filters and exports with "round" in the filter name.

SELECT qp.[query]
    ,q.[name]
    ,q.[folder]
    ,qp.*
FROM [query.part] qp
INNER JOIN [query] q
    ON q.id = qp.query
        AND q.[archived] = 0
WHERE 1 = 1
    --AND qp.[type] = 'filter'
    AND qp.[source_type] <> 'arrange'
    AND qp.[name] LIKE '%round%'
    --Exclude inactive parts
    AND qp.[active] = 1
    --Exclude queries attached to old mailings
    AND NOT EXISTS (
        SELECT *
        FROM [message.dataset] md
        LEFT JOIN [message.mailing] mm
            ON mm.[id] = md.[mailing]
                AND (
                    mm.[activated] IS NULL
                    OR mm.[deactivated] < GETDATE()
                    OR mm.[archived] = 1
                    )
        WHERE md.[query] = q.[id]
        )

What if you want to find a certain export or filter from a Query Library? When you add an export or filter from a library, Slate might store the library query's GUID in the [query.part].[xml], but there's no direct link to the original query part. Therefore, searching by name is usually the easiest approach.

In the Query where fields are used in queries Community Forum thread by Melissa Ramirez, Sarah Davis-Cagle shared a method using configurable joins originally provided by Christopher Kwan of Technolutions. While our custom sql version will tell you exactly where in the query the field is used, the CJ version only points you to the overall query, but that is most likely sufficient in most cases. With configurable joins being the preferred method for querying within Slate, we are sharing this method below.

1. Build your query on the Configurable Join - Query base.

2. Add a subquery filter, select the Aggregate to formula and paste in the following: 

q__JID_.[sql] like '%Program%'

3. Replace "Program" with the name of the field you are searching for and be sure to add some exports to your query (most likely name and guid, at the least) in order to search for the relevant queries.