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/FUNCTIONstatement (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/VIEW statements,
-- set to 'E' to convert from a CREATE script to an executable script,
-- set to 'T' to convert to a testable script.
-- V1.02.00 2022-01-25 copyright C. Pottinger
DECLARE @foundCount INT;
DECLARE @text VARCHAR(MAX);
DECLARE @name VARCHAR(100);
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;
SET @search = REPLACE(REPLACE(@search, '[', ''), ']', '');
SELECT @foundCount = COUNT(1)
FROM sys.objects
WHERE name LIKE @search
AND (type_desc LIKE '%_FUNCTION' OR
type_desc LIKE '%_STORED_PROCEDURE' OR
type_desc = 'VIEW')
IF (@foundCount = 0)
BEGIN
SELECT 'No stored procedures/functions/views match the name ''' + @search + '''';
END
IF (@foundCount > 1)
BEGIN
SELECT 'Multiple stored procedures/functions/views found. Check the results below and refine the specification' [Error];
SELECT [ss].[name] [Schema]
, [so].[type_desc]
, [so].[name]
FROM sys.objects [so]
JOIN sys.schemas [ss]
ON [ss].[schema_id] = [so].[schema_id]
WHERE [so].[name] LIKE @search
AND (type_desc LIKE '%_FUNCTION' OR
type_desc LIKE '%_STORED_PROCEDURE' OR
type_desc = 'VIEW')
AND [ss].[name] LIKE @schema;
END
IF (@foundCount = 1)
BEGIN
SELECT @text = OBJECT_DEFINITION(object_id)
, @name = '[' + DB_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)
, @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) = REPLACE(@text, CHAR(10), ' ');
DECLARE @matchStart INT;
DECLARE @matchLen INT;
DECLARE @pattern VARCHAR(50);
DECLARE @replText VARCHAR(50);
DECLARE PatternCursor CURSOR FOR
SELECT Pattern, Replacement
FROM (VALUES ('%/*%*/%', '')
, ('%--%' + @crlf + '%', '')
, ('%' + CHAR(39) + '%' + CHAR(39) + '%', '')
, ('%INSERT [@#]%', '')
, ('%INSERT INTO [@#]%', '')
, ('%UPDATE [@#]%', '')
)
AS X (Pattern, Replacement);
OPEN PatternCursor;
FETCH NEXT FROM PatternCursor INTO @pattern, @replText;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @matchLen = 1;
WHILE @matchLen > 0
BEGIN
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, [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 ABS([highEnd] - [lowEnd]) > 1
)
SELECT @matchStart = [gm].[matchPoint]
, @cleaned = IIF([gm].[matchPoint] > 0 AND [gm].[matchLen] > 0
, REPLACE(@cleaned, SUBSTRING(@cleaned, [gm].[matchPoint], [gm].[matchLen])
, @replText), @cleaned)
, @matchLen = [gm].[matchLen]
FROM (SELECT TOP 1 *
FROM cte_getMatch
WHERE ABS([highEnd] - [lowEnd]) < 2
) AS [gm];
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')
, ('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', IIF(@type='V', 'View', '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 (@type = 'V' AND @asPos > 0)
THEN '/*' + (STUFF(@text, @asPos, 4, @crlf + '*/'))
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;Edit: updated to include finding views.
Clive's YABB: lessons learned and promptly forgotten from the wonderful world of programming 
One thought on “Finding and displaying Stored Procedures, Functions, and Views”