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 PROCEDUREstatement (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;