Finding and displaying Stored Procedures


Yeah – another SQL post. I’m beginning to hate myself – but maybe this one will be useful to someone out there as well.

Quite often I want to see if a stored procedure exists, or what it does, or I just don’t remember the name of the stored procedure I have in mind. Wading through the endless list of stored procedures in MSSMS’ Object Explorer is a chore, so I created the script below.

The script has four different forms of output:

  • If more than one SP is found that matches the search criteria then a list of the matching SPs is shown as a table.
  • The SP can be shown as it is defined in the database (set @makeScript = 'C')
  • A rudimentary attempt can be made to display the SP as an executable script, rather than as a CREATE PROCEDURE statement (set @makeScript = 'E')
  • An attempt can be made display the SP as a testable script (see this entry). The script is wrapped in a transaction block to allow optional rollbacks (set @makeScript = 'T')

WARNING: The conversion of the SP to an executable script is in NO WAY guaranteed to prevent the script from performing operations that may alter the database in damaging or unexpected ways. The presentation of the store procedure as a script is intended ONLY as a convenience, meant to aid the user in creating a scripted version of the stored procedure. ALWAYS carefully examine the scripted version to ensure that it will operate as desired.

DECLARE @search     VARCHAR(50) = '%someSP%'; -- set to a LIKE expression that matches the stored procedure to find.
DECLARE @schema     VARCHAR(50) = '%'; -- set to a LIKE expression that matches the schemata to search in.
DECLARE @makeScript VARCHAR(1)  = 'C'; -- set to 'C' to show the CREATE PROCEDURE statements,
                                       -- set to 'E' to convert from a CREATE script to an executable script,
                                       -- set to 'T' to convert to a testable script. 

----------------------
DECLARE @text          VARCHAR(MAX);
DECLARE @name          VARCHAR(50);
DECLARE @atPos         INT;
DECLARE @asPos         INT;
DECLARE @crlf          VARCHAR(2) = CHAR(13) + CHAR(10);
DECLARE @testableStart VARCHAR(100) = CASE WHEN (@makeScript = 'T')
                                                THEN @crlf + 'DECLARE @mode VARCHAR(1) = ''R''' +
                                                     ' -- set to ''R'' for Report or ''U'' for update' + 
                                                     @crlf + @crlf + 'BEGIN TRANSACTION' + @crlf
                                           ELSE @crlf
                                      END;
DECLARE @testableEnd   VARCHAR(300) = CASE WHEN (@makeScript = 'T')
                                                THEN @crlf + @crlf + 
                                                     'IF @mode = ''U''' + @crlf +
                                                     'BEGIN' + @crlf +
                                                     '  COMMIT TRANSACTION' + @crlf +
                                                     '  SELECT ''Changes have been applied to the '' + 
                                                        DB_NAME() + '' database'' [notice]' + @crlf +
                                                     'END' + @crlf +
                                                     'ELSE' + @crlf +
                                                     'BEGIN' + @crlf +
                                                     '  ROLLBACK TRANSACTION' + @crlf +
                                                     '  SELECT ''All changes have been rolled back from '' +
                                                        DB_NAME() [notice]' + @crlf +
                                                     'END'
                                           ELSE ''
                                      END;

IF (SELECT COUNT(1) FROM sys.procedures where name like @search) > 1
BEGIN
  SELECT 'Multiple stored procedures found. Check the results below and refine the specification' [Error];
  SELECT [ss].name [Schema]
        ,[sp].name [Stored Procedure]
    FROM sys.procedures [sp] 
         JOIN sys.schemas [ss]
           ON [ss].[schema_id] = [sp].[schema_id]
   WHERE [sp].[name] LIKE @search
         AND [ss].[name] LIKE @schema; 
END
ELSE BEGIN
  SELECT @text = OBJECT_DEFINITION(object_id) 
        ,@name = [sp].[name]
    FROM sys.procedures [sp] 
         JOIN sys.schemas [ss]
           ON [ss].[schema_id] = [sp].[schema_id]
   WHERE [sp].[name] LIKE @search
         AND [ss].[name] LIKE @schema; 

  IF @makeScript = 'T' OR @makeScript = 'E'
  BEGIN
    SELECT @atPos   = CHARINDEX('@', @text, CHARINDEX(@name, @text))
          ,@asPos   = CHARINDEX(@crlf + 'AS' + @crlf, @text, @atPos)
          ,@text    = '-- ' +
                      CASE WHEN (@makeScript = 'E') THEN 'Executable' ELSE 'Testable' END +
                      ' script from ' + @name + @crlf + @crlf + 
                      CASE WHEN (@atPos > 0 AND @atPos < @asPos)
                                THEN STUFF(STUFF(@text, @asPos, 4, ';' + @testableStart), 1, @atPos - 1, 'DECLARE ')
                           WHEN (@asPos > 0)
                                THEN STUFF(@text, 1, @asPos + 4, @testableStart)
                           ELSE @text
                      END 
                      + @testableEnd;
  END;
  
  PRINT @text;
END;

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s