Search Procedure Text

This has to be one of the most common repetitive functions needed for development. Updating a certain column in a specific table and want to find all the procedures that are affected by that? You’ll need to use an object search. There are a few different methods for doing this. The ANSI standard method using the INFORMATION_SCHEMA.ROUTINES system views have not in the past contained all of the stored procedure text. Only the first 8000 characters or so. (If I’m wrong on that, don’t challenge me because I don’t care).

Simple snippet

SELECT sm.*, so.*
FROM sys.sql_modules sm WITH (NOLOCK)
INNER JOIN sys.objects so WITH (NOLOCK)
ON sm.[object_id] = so.[object_id]
WHERE sm.definition LIKE '%table_name%'

However if you’re going to be using this search a lot, and I can’t imagine you’re not, then why not encapsulate it in a stored procedure so you can include multiple terms, exclude terms, and sort. You will have to place this procedure in each database you want to search however.

Stored Procedure: sp_search

USE master
GO

CREATE PROCEDURE spsearch
    @include VARCHAR(MAX) = NULL,
    @sort VARCHAR(5) = 1, -- 1 ='name_asc' 2 = 'name_desc' 3 = 'date_desc', 4 = 'date_asc'
    @exclude VARCHAR(MAX) = NULL
   
AS
-- EXEC sp_search 'UPDATE,products,status', 3
BEGIN

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @Delimiter VARCHAR(MAX) = ','

    IF OBJECT_ID(N'tempdb..#split') IS NOT NULL DROP TABLE #split

    ;WITH split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@delimiter,@include) AS endpos
        UNION ALL
        SELECT CAST(endpos+1 AS INT), CHARINDEX(@delimiter,@include,endpos+1)
            FROM split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = '%' + SUBSTRING(@include,stpos,COALESCE(NULLIF(endpos,0),LEN(@include)+1)-stpos) + '%'
    INTO #split
    FROM split;
   
    IF ((SELECT MAX(id) FROM #split) > 3)
    BEGIN
        SELECT 'cannot specify more than 3 include criteria'
        RETURN
    END
   
    ;WITH split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@delimiter,@exclude) AS endpos
        UNION ALL
        SELECT CAST(endpos+1 AS INT), CHARINDEX(@delimiter,@exclude,endpos+1)
            FROM split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = '%' + SUBSTRING(@exclude,stpos,COALESCE(NULLIF(endpos,0),LEN(@exclude)+1)-stpos) + '%'
    INTO #split_exclude
    FROM split
    WHERE @exclude IS NOT NULL;
   
    IF ((SELECT MAX(id) FROM #split_exclude) > 3)
    BEGIN
        SELECT 'cannot specify more than 3 exclude criteria'
        RETURN
    END


    SELECT
        so.name,
        so.type_desc,
        so.create_date,
        so.modify_date,
        sm.[definition]
    FROM sys.sql_modules sm WITH (NOLOCK)
    INNER JOIN sys.objects so WITH (NOLOCK)
    ON so.[object_id] = sm.[object_id]
    WHERE
    (
        (
            NOT EXISTS (SELECT DATA FROM #split WHERE id = 1)
            OR
            sm.definition LIKE (SELECT DATA FROM #split WHERE id = 1)
        )
        AND
        (
            NOT EXISTS (SELECT DATA FROM #split WHERE id = 2)
            OR
            sm.definition LIKE (SELECT DATA FROM #split WHERE id = 2)
        )
        AND
        (
            NOT EXISTS (SELECT DATA FROM #split WHERE id = 3)
            OR
            sm.definition LIKE (SELECT DATA FROM #split WHERE id = 3)
        )
    )
    AND
    (
        (
            NOT EXISTS (SELECT DATA FROM #split_exclude WHERE id = 1)
            OR
            sm.definition NOT LIKE (SELECT DATA FROM #split_exclude WHERE id = 1)
        )
        AND
        (
            NOT EXISTS (SELECT DATA FROM #split_exclude WHERE id = 2)
            OR
            sm.definition NOT LIKE (SELECT DATA FROM #split_exclude WHERE id = 2)
        )
        AND
        (
            NOT EXISTS (SELECT DATA FROM #split_exclude WHERE id = 3)
            OR
            sm.definition NOT LIKE (SELECT DATA FROM #split_exclude WHERE id = 3)
        )      
    )
    ORDER BY
        CASE WHEN @sort = '1' THEN so.name END ASC,
        CASE WHEN @sort = '2' THEN so.name END DESC,
        CASE WHEN @sort = '3' THEN so.modify_date END DESC,
        CASE WHEN @sort = '3' THEN so.create_date END ASC
       

END

Example Usage

  • first parameter is a comma delimited list of phrases you want to match. You do not need to add the wildcard % I did it for you in the code.
  • second parameter is a sort option. — 1 =’name_asc’ 2 = ‘name_desc’ 3 = ‘date_desc’, 4 = ‘date_asc’
  • third parameter is a comma delimited list of phrases you want to exclude
-- look for the three words listed and sort base modify date desc:
EXEC sp_search 'UPDATE,products,status', 3
-- look for the three words listed and sort base name asc, and exclude a single word:
EXEC sp_search 'UPDATE,products,status', 2, 'customerid'

That’s it!

Featured Articles

 Site Author

  • Thanks for visiting!