Find text in any table or any stored procedure

Typically I want to find text somewhere in a table or a stored procedure. There are plugins that exist to help you search — but in a pinch you can search the database manually too.

Find text referenced in any table.

SELECT DISTINCT object_name (id) AS object, text
FROM sys.syscomments ssc
    INNER JOIN INFORMATION_SCHEMA.TABLES isr ON isr.TABLE_NAME = object_name(ssc.id)
WHERE text LIKE '%changeme%';

Find text referenced in any stored procedure

SELECT DISTINCT object_name (id) AS object, text
FROM sys.syscomments ssc
    INNER JOIN INFORMATION_SCHEMA.ROUTINES isr ON isr.ROUTINE_NAME = object_name(ssc.id)
WHERE text LIKE '%changeme%'
    AND isr.ROUTINE_TYPE = 'PROCEDURE';

Find text (e.g. stored proc or table name) referenced anywhere in the database. This query tends to not be specific enough, so I find myself using it rarely.

SELECT DISTINCT object_name (id) AS object, text
FROM sys.syscomments
WHERE text LIKE '%changeme%';
Default image
Eric
Code guy.

Newsletter Updates

Enter your email address below to subscribe to our newsletter

Leave a Reply