Search Text In Database Procedures

Posted: November 3, 2008 in SQL Server 2005

To search for a particular text in all the stored procedures in a database, execute the following code:

-- Declare variables
DECLARE @SearchString VARCHAR(100);

-- Intialise variables
SET @SearchString = '';

-- Create a temporary table to hold the procedure name and procedure text
CREATE TABLE #tmpProcedureNameText
            (ProcedureText VARCHAR(MAX)
            ,ProcedureName VARCHAR(100)
            );

-- Create a temporay table to hold all the procedure names in the database
CREATE TABLE #tmpProcedureName
            (ID            INT IDENTITY
            ,ProcedureName VARCHAR(100)
            );

-- Get the list of all the procedures in the database
INSERT #tmpProcedureName
    SELECT [Name]
    FROM   [Sys].[Procedures];

-- Declare variable to hold the number of procedures
DECLARE @NumberOfProcs INT;

-- Get the number of procedures that were inserted
SELECT @NumberOfProcs = COUNT(*)
FROM   [Sys].[Procedures];

-- Get the text for each of the procedures that were inserted
DECLARE @i INT;
SET @i = 1;

DECLARE @ProcedureName VARCHAR(100);
DECLARE @StringSQL     VARCHAR(MAX);

-- Loop through until all the procedures have been evaludated
WHILE @i <= @NumberOfProcs
    BEGIN
        -- Select the procedure to use
        SELECT @ProcedureName = ProcedureName
        FROM   #tmpProcedureName
        WHERE  ID = @i;
        -- Build the SQL string and execute it
        SET @StringSQL = 'INSERT INTO #tmpProcedureNameText (ProcedureText)
                          EXEC sp_helptext ' + @ProcedureName;
        EXEC (@StringSQL);
        -- Update the record for the procedure
        UPDATE #tmpProcedureNameText
        SET    ProcedureName = @ProcedureName
        WHERE  ProcedureName IS NULL;
        -- increment the count
        SET @i = @i + 1;
    END;

-- Search for the string that you declared above
SELECT *
FROM   #tmpProcedureNameText
WHERE  ProcedureText LIKE '%' + @SearchString + '%'

-- Clean up the tables
DROP TABLE #tmpProcedureNameText;
DROP TABLE #tmpProcedureName;
Advertisements

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