Finding and displaying Stored Procedures and Functions


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 or function 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 procedure or function is found that matches the search criteria then a list of the matching objects is shown as a table.
  • The stored procedure or function can be shown as it is defined in the database (set @makeScript = 'C')
  • A rudimentary attempt can be made to display the stored procedure or function as an executable script, rather than as a CREATE PROCEDURE/FUNCTION statement (set @makeScript = 'E')
  • An attempt can be made display the stored procedure or function 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 code 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 code as a script is intended ONLY as a convenience, meant to aid the user in creating a scripted version of the code. ALWAYS carefully examine the scripted version to ensure that it will operate as desired.

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

-- V1.01.05 2021-08-04 copyright C. Pottinger
DECLARE @foundCount    INT;
DECLARE @text          VARCHAR(MAX);
DECLARE @name          VARCHAR(50);
DECLARE @type          VARCHAR(5);
DECLARE @atPos         INT;
DECLARE @retPos        INT;
DECLARE @parmEnd       INT;
DECLARE @asPos         INT;
DECLARE @warns         VARCHAR(MAX);
DECLARE @crlf          VARCHAR(2) = CHAR(13) + CHAR(10);
DECLARE @testableStart VARCHAR(MAX) = 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(MAX) = 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;
                                      
SELECT @foundCount = COUNT(1) 
  FROM sys.objects 
 WHERE NAME LIKE @search 
       AND (type_desc LIKE '%_FUNNCTION' OR
            type_desc LIKE '%_STORED_PROCEDURE') 
IF (@foundCount = 0)
BEGIN
  SELECT 'No stored procedures/functions match the name ''' + @search + '''';
END
IF (@foundCount > 1)
BEGIN
  SELECT 'Multiple stored procedures/functions found. Check the results below and refine the specification' [Error];
  SELECT [ss].[name] [Schema]
       , CASE WHEN ([so].[type] = 'P') THEN 'Stored Procedure' ELSE 'Function' END
       , [so].[name] 
    FROM sys.objects [so] 
         JOIN sys.schemas [ss]
           ON [ss].[schema_id] = [so].[schema_id]
   WHERE [so].[name] LIKE @search
         AND [ss].[name] LIKE @schema; 
END
IF (@foundCount = 1)
BEGIN
  SELECT @text = OBJECT_DEFINITION(object_id) 
       , @name = '[' + [ss].[name] + '].[' + [so].[name] + ']'
       , @type = [so].[type]
    FROM sys.objects [so] 
         JOIN sys.schemas [ss]
           ON [ss].[schema_id] = [so].[schema_id]
   WHERE [so].[name] LIKE @search
         AND [ss].[name] LIKE @schema; 

  IF @makeScript = 'T' OR @makeScript = 'E'
  BEGIN
    SELECT @atPos   = CHARINDEX('@', @text, CHARINDEX(@name, @text))
         , @retPos  = CHARINDEX('RETURNS ', @text, @atPos) + CHARINDEX('WITH ', @text, @atPos)
         , @parmEnd = @retPos -  CHARINDEX(')', REVERSE(SUBSTRING(@text, 1, @retPos)))
         , @asPos   = PATINDEX('%[ ' + @crlf + ']AS[ ' + @crlf + ']%', @text);

    -- Clean the definition of any string literals and comments so we can properly check for
    -- dangerous statements
    DECLARE @cleaned    VARCHAR(MAX) = @text;
    DECLARE @matchStart INT;
    DECLARE @matchLen   INT;
    DECLARE @pattern    VARCHAR(50);
    DECLARE @replText   VARCHAR(50);
    
    --declare @matched varchar(max); -- debugging line
    --declare @forpat varchar(50);   -- debugging line
    
    DECLARE PatternCursor CURSOR FOR
      SELECT Pattern, Replacement
        FROM (VALUES ('%/*%*/%', '') 
                   , ('%--%' + @crlf + '%', '')
                   , ('%' + CHAR(39) + '%' + CHAR(39) + '%', '')
                   , ('%INSERT [@#]%', '')
                   , ('%INSERT INTO [@#]%', '')
             )
          AS X (Pattern, Replacement);
    OPEN PatternCursor;
    FETCH NEXT FROM PatternCursor INTO @pattern, @replText;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @matchStart = 1;
        WHILE @matchStart > 0
        BEGIN
            SET @matchStart = 0;
            WITH cte_getMatch
            AS (SELECT PATINDEX(@pattern, @cleaned)                                  AS [matchPoint]
                     , 0                                                             AS [matchLen]
                     , 0                                                             AS [lowEnd]
                     , LEN(@cleaned)                                                 AS [highEnd] 
                     , LEN(@cleaned) / 2                                             AS [newEnd]
                     , PATINDEX(@pattern, SUBSTRING(@cleaned, 1, LEN(@cleaned) / 2)) AS [foundInTestArea]
                 UNION ALL
                SELECT [matchPoint]                                                     AS [matchPoint]
                     , CAST(IIF([foundInTestArea] = 0                                   
                                , [matchLen]                                            
                                , [newEnd] - [matchPoint] + 1                           
                               ) AS INT                                                 
                           )                                                            AS [matchLen]
                     , CAST(IIF([foundInTestArea] = 0                                   
                                , IIF([matchPoint] > [newEnd], [matchPoint], [newEnd])                                              
                                , IIF([matchPoint] > [lowEnd], [matchPoint], [lowEnd])  
                               ) AS INT                                                 
                           )                                                            AS [lowEnd]
                     , IIF([foundInTestArea] = 0, [highEnd], [newEnd] )                 AS [highEnd]
                     , ([newEnd] + IIF([foundInTestArea] = 0, [highEnd], [lowEnd])) / 2 AS [newEnd]
                     , PATINDEX(@pattern
                                , SUBSTRING(@cleaned
                                            , 1
                                            , ([newEnd] + IIF([foundInTestArea] = 0
                                                              , [highEnd]
                                                              , [lowEnd]
                                                             )
                                              ) / 2
                                           )
                               )                                                        AS [foundInTestArea]
                  FROM cte_getMatch
                 WHERE [matchPoint] > 0
                       AND ABS([highEnd] - [lowEnd]) > 1                  
               )
               --select *, SUBSTRING(@cleaned, [gm].[matchPoint], [gm].[matchLen] [Matched]) -- debugging line
               --  from cte_getMatch as gm;                                                  -- debugging line
            SELECT @matchStart = [gm].[matchPoint]
                 , @cleaned    = IIF([gm].[matchPoint] > 0 AND [gm].[matchLen] > 0
                                     , REPLACE(@cleaned
                                               , SUBSTRING(@cleaned, [gm].[matchPoint], [gm].[matchLen]), @replText
                                              )
                                     , @cleaned
                                    )
                 --, @matched = SUBSTRING(@text, [gm].[matchPoint], [gm].[matchLen]) -- debugging line
                 --, @forpat  = @pattern                                             -- debugging line
                 , @matchLen = [gm].[matchLen]
              FROM (SELECT TOP 1 * 
                      FROM cte_getMatch
                     WHERE @matchLen > 0
                           AND ABS([highEnd] - [lowEnd]) < 2
                   ) AS [gm]; 
            --select @matchStart [@matchStart], @matchLen [@matchLen], -- debugging line
            --       @matched [matched], @forpat [for pattern];        -- debugging line
        END;
    
        FETCH NEXT FROM PatternCursor INTO @pattern, @replText;
    END;
    CLOSE PatternCursor;
    DEALLOCATE PatternCursor;

    SELECT @warns = STUFF((SELECT ', ' + RTRIM(DANGEROUS.statement) -- for use with SQL Server 2016 and earlier
    --SELECT @warns = (SELECT STRING_AGG(statement, ', ')             -- for use with SQL Server 2017 and later
                       FROM (VALUES ('ALTER TABLE')
                                  , ('COMMIT TRANSACTION')
                                  , ('CREATE INDEX')
                                  , ('CREATE TABLE')
                                  , ('CREATE VIEW')
                                  , ('DELETE ')
                                  , ('DROP INDEX')
                                  , ('DROP TABLE')
                                  , ('DROP VIEW')
                                  , ('EXEC ')
                                  , ('EXECUTE ')
                                  , ('GRANT ')
                                  , ('INSERT ')
                                  , ('PREPARE TRANSACTION')
                                  , ('REVOKE ')
                                  , ('ROLLBACK TRANSACTION ')
                                  , ('TRUNCATE TABLE ')
                                  , ('UPDATE ')
                                  , ('USE ')) AS DANGEROUS(statement)
	               WHERE CHARINDEX(statement, @cleaned, CHARINDEX(@name, @cleaned)) > 0
                       FOR XML PATH('')), 1, 2, ''              -- for use with SQL Server 2016 and earlier
                   );


    SELECT @text = '-- ' +
                   IIF(@makeScript = 'E', 'Executable', 'Testable') +
                   ' script from ' + 
                   IIF(@type = 'P', 'Stored Procedure', 'Function') +
                   ' ' + @name + @crlf + 
                   IIF(@warns IS NULL,
                       '', 
                       '-- WARNING: script contains dangerous statements: ' + @warns + @crlf
                      ) +
                   CASE WHEN (@atPos > 0 AND @atPos < @asPos AND @retPos = 0)
                             THEN STUFF
                                  (  STUFF(@text, @asPos, 4, ';' + @testableStart)
                                   , 1 , @atPos - 1 , 'DECLARE '
                                  )
                        WHEN (@atPos > 0 AND @atPos < @asPos AND @retPos > 0)
                             THEN STUFF
                                  (  STUFF
                                     (  STUFF(@text, @asPos, 4, @crlf + '*/' + @testableStart)
                                      , @parmEnd, @retPos - @parmEnd, ';' + @crlf + '/* '
                                     )
                                   , 1, @atPos - 1, 'DECLARE '
                                  )
                        WHEN (@asPos > 0)
			     THEN STUFF(@text, 1, @asPos + 4, @testableStart)
                        ELSE @text
                   END 
                   + @testableEnd;
  END;
  
  DECLARE @index INT
  WHILE (LEN(@text) > 4096)
  BEGIN
    SET @index = 4095 - CHARINDEX(@crlf, REVERSE(SUBSTRING(@text, 1, 4096)));
    SET @index = CASE WHEN (@index = 0) THEN 4095 ELSE @index END;
    PRINT SUBSTRING(@text, 1, @index);
    SET @text = SUBSTRING(@text, @index + 1, LEN(@text));
  END;
  PRINT @text;
END;

One thought on “Finding and displaying Stored Procedures and Functions”

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