SpDev Install free

How to find text in stored procedures in SQL Server

Search the code of every stored procedure, function, view and trigger for a table, column or phrase. Why INFORMATION_SCHEMA misses matches, how to search all databases, and how to find real dependencies.

By Areeb Ahmer, . 4 minute read.

You’re about to rename a column, drop a table or change what a status code means, and you need to know which procedures use it. SQL Server keeps the source of every procedure, function, view and trigger, so you can search it with a query. The trick is to search the right catalog view, because one of the obvious ones silently misses matches.

The examples run against the SpDevDemo database.

The query

DECLARE @find NVARCHAR(200) = N'Cancelled';

SELECT
    s.name        AS schema_name,
    o.name        AS object_name,
    o.type_desc,
    o.modify_date
FROM sys.sql_modules AS m
JOIN sys.objects     AS o ON o.object_id = m.object_id
JOIN sys.schemas     AS s ON s.schema_id = o.schema_id
WHERE m.definition LIKE N'%' + @find + N'%'
ORDER BY o.type_desc, s.name, o.name;

sys.sql_modules.definition holds the full text of every T-SQL module in the database: procedures, functions, views, triggers. It’s NVARCHAR(MAX), so nothing is cut off. The search is case-insensitive when the database collation is.

To show where in the code the match is, add a snippet:

SUBSTRING(m.definition, CHARINDEX(@find, m.definition) - 60, 160) AS context

Why not INFORMATION_SCHEMA.ROUTINES?

It’s the first answer you’ll find online, and it misses matches. INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION holds only the first 4,000 characters of each routine. Anything after that isn’t searched.

Create a procedure of about 6,100 characters with the word near the end, and compare:

DECLARE @sql NVARCHAR(MAX) =
    N'CREATE OR ALTER PROCEDURE dbo.LongProcedure AS BEGIN '
    + REPLICATE(CAST(N'SELECT 1 AS filler; ' AS NVARCHAR(MAX)), 300)
    + N'SELECT OrderId FROM dbo.Orders WHERE Status = ''Cancelled''; END';
EXEC (@sql);

SELECT
    LEN(OBJECT_DEFINITION(OBJECT_ID('dbo.LongProcedure')))                           AS full_length,
    (SELECT LEN(ROUTINE_DEFINITION) FROM INFORMATION_SCHEMA.ROUTINES
      WHERE ROUTINE_NAME = 'LongProcedure')                                          AS information_schema_length,
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES
      WHERE ROUTINE_NAME = 'LongProcedure' AND ROUTINE_DEFINITION LIKE '%Cancelled%') AS found_by_information_schema,
    (SELECT COUNT(*) FROM sys.sql_modules
      WHERE object_id = OBJECT_ID('dbo.LongProcedure') AND definition LIKE '%Cancelled%') AS found_by_sql_modules;
full_length  information_schema_length  found_by_information_schema  found_by_sql_modules
-----------  -------------------------  ---------------------------  --------------------
6108         4000                       0                            1

The old sys.syscomments view has a similar problem: it splits long definitions into 4,000-character rows, so a word that straddles two rows isn’t found. Use sys.sql_modules, or OBJECT_DEFINITION(object_id), which returns the same full text.

Searching for names with brackets or underscores

LIKE treats _ as “any one character”, % as “anything”, and [...] as a set of characters. Searching for Order_Lines also matches OrderXLines, and searching for [Orders] matches any single letter from that set. Escape them:

SELECT o.name
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%Order[_]Lines%'          -- [_] is a literal underscore
   OR m.definition LIKE N'%[[]Orders]%';             -- [[] is a literal [

For an exact substring with no wildcards at all, CHARINDEX(@find, m.definition) > 0 avoids the question.

Searching every database

Catalog views are per database. To search all of them, loop over sys.databases with dynamic SQL:

DECLARE @find NVARCHAR(200) = N'Cancelled';
DECLARE @sql  NVARCHAR(MAX) = N'';

SELECT @sql += N'
SELECT ' + QUOTENAME(name, '''') + N' AS database_name, s.name AS schema_name, o.name AS object_name, o.type_desc
FROM ' + QUOTENAME(name) + N'.sys.sql_modules AS m
JOIN ' + QUOTENAME(name) + N'.sys.objects AS o ON o.object_id = m.object_id
JOIN ' + QUOTENAME(name) + N'.sys.schemas AS s ON s.schema_id = o.schema_id
WHERE CHARINDEX(@find, m.definition) > 0
UNION ALL'
FROM sys.databases
WHERE database_id > 4                 -- skip master, tempdb, model, msdb
  AND state_desc = 'ONLINE'
  AND HAS_DBACCESS(name) = 1;

IF @sql <> N''
BEGIN
    SET @sql = LEFT(@sql, LEN(@sql) - LEN(N'UNION ALL')) + N' ORDER BY database_name, object_name;';
    EXEC sp_executesql @sql, N'@find NVARCHAR(200)', @find = @find;
END

sp_MSforeachdb is often suggested for this. It’s undocumented, and it has been known to skip databases, so the loop above is the safer choice.

Text search vs real dependencies

Text search finds the word everywhere: in comments, in strings, in other tables’ names (Orders matches OrderLines too). When the question is “what uses this table or column”, SQL Server’s dependency views are more precise:

SELECT
    r.referencing_schema_name,
    r.referencing_entity_name,
    d.referenced_minor_name AS column_name
FROM sys.dm_sql_referencing_entities('dbo.Orders', 'OBJECT') AS r
CROSS APPLY sys.dm_sql_referenced_entities(
    QUOTENAME(r.referencing_schema_name) + '.' + QUOTENAME(r.referencing_entity_name), 'OBJECT') AS d
WHERE d.referenced_entity_name = 'Orders'
  AND d.referenced_minor_name = 'Status';
referencing_schema_name  referencing_entity_name  column_name
-----------------------  -----------------------  -----------
dbo                      LongProcedure            Status

That lists every module that references the Status column of dbo.Orders. sys.dm_sql_referenced_entities raises an error if one of the modules refers to an object that no longer exists; that’s worth knowing about anyway. The dependency views can’t see table names inside dynamic SQL strings. Use both: dependencies for precision, text search to catch dynamic SQL and comments.

Beyond the database

Not all T-SQL lives in modules. Also check:

  • SQL Server Agent jobs: msdb.dbo.sysjobsteps.command.
  • Your application code for inline SQL and ORM mappings.
  • SSIS packages and reports, which embed their own queries.
  • Your source control. If every procedure lives as a .sql file in a repository, a plain text search in your editor covers everything at once, including procedures that haven’t been deployed yet. That’s also how SpDev works: it debugs and profiles procedures from their .sql files in your workspace.