Searching TSQL Stored Procedures (and other Modules)

Every now and then I come across the question, “How do I search my stored procedures for a certain table or column name in MS SQL Server?”. You might have, too…

SQL Server up to version 2000 provided the view syscomments, which is still implemented in versions 2005 and higher as sys.syscomments. (All system catalog views have been moved to the sys schema in 2005)

The major drawback with syscomments is that its text column containing the stored procedure code is defined as NVARCHAR(4000), the longest possible string value in SQL Server 2000 (apart from NTEXT). So if the procedure (or function) code is longer than 4000 characters, the code is sliced into several 4000 character records. And as it happens, the string you are searching for may end up right at the boundary of two records, complicating the search algorithm.

SQL Server 2005 introduced the sys.sql_modules view which has a Definition NVARCHAR(MAX) column holding code, thus resolving this issue of split code. (check the remarks if you want to search DEFAULT and CHECK expressions, too)

So now that you happily accepted that search sql_modules is better than searching syscomments, here is the code to perform the search in TSQL:

DECLARE @Search NVARCHAR(MAX)

SET @Search = 'MyOldColumnName'

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, -- o.type,
       o.type_desc   -- , sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition LIKE N'%' + @Search + N'%'
ORDER BY o.type, OBJECT_NAME(sm.object_id);

As you see, the search is executed using the LIKE operation. But what if you want to search for wildcards as literals, such as _ and %, or the regex characters [, ], –  (because, for example, your database object names may contain underscores) ?

Simply escape the characters using the [] notation:

SET @Search = REPLACE(@Search, '[', '[[]')
SET @Search = REPLACE(@Search, '%', '[%]')
SET @Search = REPLACE(@Search, '_', '[_]')

and run the query after the REPLACE operations.

But what if you want to search for whole words only? Use PATINDEX using a regex which excludes alphabetical characters and underscores before and after the search string:

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, 
    o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE PATINDEX(N'%[^a-zA-Z_]' + @Search + N'[^a-zA-Z_]%', sm.definition) > 0
ORDER BY o.type, OBJECT_NAME(sm.object_id);

One Response to Searching TSQL Stored Procedures (and other Modules)

  1. Don Schaeffer says:

    Sometimes I want to see the occurrences of the string within the object, and not just the first occurrence; the first occurrence may be within a comment and the string I’m searching for may or may not actually be used. Here’s a recursive CTE that does this:

    DECLARE @SearchString varchar(80)

    SELECT @SearchString = ‘%mywave%’;

    WITH Search AS
    (
    SELECT
    s.name + ‘.’ + o.name as ObjectName,
    1 as Sequence,
    patindex(@SearchString, c.Definition) as Location,
    substring(c.Definition, patindex(@SearchString,c.definition),99999) AS Definition
    FROM sys.sql_modules c
    JOIN sys.objects o on c.object_id = o.object_id
    JOIN sys.schemas s on o.schema_id = s.schema_id
    WHERE c.definition like @SearchString

    UNION ALL

    SELECT
    ObjectName,
    Sequence + 1 as Sequence,
    Location + patindex(@SearchString, SUBSTRING(Definition,2,99999)) as Location,
    substring(Definition, patindex(@SearchString, SUBSTRING(Definition,2,99999))+ 1,99999) AS Definition — start next search one position to the right
    FROM Search
    WHERE patindex(@SearchString, SUBSTRING(Definition,2,99999)) 0

    )
    SELECT * FROM Search
    ORDER BY ObjectName, Location

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: