LLI Wiki — My Digital Knowledge Base

Find modules that reference an object and databases that hold a table

T-SQL queries to find procedures, views and functions that reference an object, and the databases that hold a table.

These queries answer three questions: which modules reference an object, which database holds a table, and which online databases contain a given table.

Find the modules that reference an object

The query searches the text of every module (stored procedure, function, view, trigger) for a schema-qualified name, in its four bracket variants.

DECLARE @SCHEMA_NAME VARCHAR(100) = 'dbo';
DECLARE @OBJECT_NAME VARCHAR(100) = 'CommandExecute';

SELECT
    sys.objects.object_id,
    sys.schemas.name AS [Schema], 
    sys.objects.name AS Object_Name, 
    sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK) 
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id 
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
    (
           '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
    )
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

Find the database that holds a table

sp_MSforeachdb 'SELECT ''?'' AS DB, * FROM [?].sys.tables WHERE name LIKE ''%tablename%'''

Find the online databases that contain a table

SELECT name, state_desc
   FROM   sys.databases 
   WHERE  CASE
        WHEN state_desc = 'ONLINE' 
        THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[TableName]', 'U')
   END IS NOT NULL

Notes

Mastodon LLIMastodon SocialFosstodon