How to find specific Prepackaged Access Reports queries in Voyager based on certain strings

Question

Need help finding specific queries based on text strings in Prepackaged Access Reports.

Answer

The following may be helpful in trying to find specific queries based on a text string.

List queries that use the specified table:

SELECT MSysQueries.Name1 AS [Table Name], MSysObjects.Name AS [Queries that use the table]
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE (((MSysQueries.Name1) Like [Table name (use * to truncate): ])
AND ((MSysQueries.Attribute)=5))
ORDER BY MSysObjects.Name;

List queries that display the specified field:

SELECT MSysQueries.Expression AS [Field name], MSysObjects.Name AS [Query that displays this field]
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE (((MSysQueries.Attribute)=6) AND ((MSysQueries.Expression)
Like [Table name (use * to truncate): ]+'.'+[Field name (use * to truncate):]))
ORDER BY MSysQueries.Expression;

List queries that contain a certain string in a criterion:

SELECT MSysQueries.Expression AS Criterion, MSysObjects.Name AS [Queries that use this criterion]
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE (((MSysQueries.Attribute)=8) AND
((MSysQueries.Expression) Like '*'+[Part of a criterion: ]+'*'))
ORDER BY MSysObjects.Name;

List queries with specified string in query name:

SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE (((UCase([Name])) Like '*'+UCase([Part of a query name: ])+'*') AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

Additional Information

See also: List Voyager Prepackaged Access Reports queries added or modified since a specific date

• Article last edited: 01-Jun-2021