Search Procedure Text

This has to be one of the most common repetitive functions needed for development. Updating a certain column in a specific table and want to find all the procedures that are affected by that? You’ll need to use an object search. There are a few different methods for doing this. The ANSI standard method using the INFORMATION_SCHEMA.ROUTINES system views have not in the past contained all of the stored procedure text. Only the first 8000 characters or so. (If I’m wrong on that, don’t challenge me because I don’t care).

Simple snippet

[cc lang=”sql”]
SELECT sm.*, so.*
FROM sys.sql_modules sm WITH (NOLOCK)
INNER JOIN sys.objects so WITH (NOLOCK)
ON sm.[object_id] = so.[object_id]
WHERE sm.definition LIKE ‘%table_name%’
[/cc]

However if you’re going to be using this search a lot, and I can’t imagine you’re not, then why not encapsulate it in a stored procedure so you can include multiple terms, exclude terms, and sort. You will have to place this procedure in each database you want to search however.

Stored Procedure: sp_search

[cc lang=”sql”]
USE master
GO

CREATE PROCEDURE spsearch
@include varchar(max) = NULL,
@sort varchar(5) = 1, — 1 =’name_asc’ 2 = ‘name_desc’ 3 = ‘date_desc’, 4 = ‘date_asc’
@exclude varchar(max) = NULL

AS
— EXEC sp_search ‘UPDATE,products,status’, 3
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @Delimiter varchar(max) = ‘,’

IF OBJECT_ID(N’tempdb..#split’) IS NOT NULL DROP TABLE #split

;WITH split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@delimiter,@include) AS endpos
UNION ALL
SELECT CAST(endpos+1 as int), CHARINDEX(@delimiter,@include,endpos+1)
FROM split
WHERE endpos > 0
)
SELECT ‘Id’ = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
‘Data’ = ‘%’ + SUBSTRING(@include,stpos,COALESCE(NULLIF(endpos,0),LEN(@include)+1)-stpos) + ‘%’
INTO #split
FROM split;

IF ((SELECT MAX(id) FROM #split) > 3)
BEGIN
SELECT ‘cannot specify more than 3 include criteria’
RETURN
END

;WITH split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@delimiter,@exclude) AS endpos
UNION ALL
SELECT CAST(endpos+1 as int), CHARINDEX(@delimiter,@exclude,endpos+1)
FROM split
WHERE endpos > 0
)
SELECT ‘Id’ = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
‘Data’ = ‘%’ + SUBSTRING(@exclude,stpos,COALESCE(NULLIF(endpos,0),LEN(@exclude)+1)-stpos) + ‘%’
INTO #split_exclude
FROM split
WHERE @exclude IS NOT NULL;

IF ((SELECT MAX(id) FROM #split_exclude) > 3)
BEGIN
SELECT ‘cannot specify more than 3 exclude criteria’
RETURN
END

SELECT
so.name,
so.type_desc,
so.create_date,
so.modify_date,
sm.[definition]
FROM sys.sql_modules sm WITH (NOLOCK)
INNER JOIN sys.objects so WITH (NOLOCK)
ON so.[object_id] = sm.[object_id]
WHERE
(
(
NOT EXISTS (SELECT data FROM #split WHERE id = 1)
OR
sm.definition LIKE (SELECT data FROM #split WHERE id = 1)
)
AND
(
NOT EXISTS (SELECT data FROM #split WHERE id = 2)
OR
sm.definition LIKE (SELECT data FROM #split WHERE id = 2)
)
AND
(
NOT EXISTS (SELECT data FROM #split WHERE id = 3)
OR
sm.definition LIKE (SELECT data FROM #split WHERE id = 3)
)
)
AND
(
(
NOT EXISTS (SELECT data FROM #split_exclude WHERE id = 1)
OR
sm.definition NOT LIKE (SELECT data FROM #split_exclude WHERE id = 1)
)
AND
(
NOT EXISTS (SELECT data FROM #split_exclude WHERE id = 2)
OR
sm.definition NOT LIKE (SELECT data FROM #split_exclude WHERE id = 2)
)
AND
(
NOT EXISTS (SELECT data FROM #split_exclude WHERE id = 3)
OR
sm.definition NOT LIKE (SELECT data FROM #split_exclude WHERE id = 3)
)
)
ORDER BY
CASE WHEN @sort = ‘1’ THEN so.name END ASC,
CASE WHEN @sort = ‘2’ THEN so.name END DESC,
CASE WHEN @sort = ‘3’ THEN so.modify_date END DESC,
CASE WHEN @sort = ‘3’ THEN so.create_date END ASC

END
[/cc]

Example Usage

• first parameter is a comma delimited list of phrases you want to match. You do not need to add the wildcard % I did it for you in the code.
• second parameter is a sort option. — 1 =’name_asc’ 2 = ‘name_desc’ 3 = ‘date_desc’, 4 = ‘date_asc’
• third parameter is a comma delimited list of phrases you want to exclude

[cc lang=”sql”]
— look for the three words listed and sort base modify date desc:
EXEC sp_search ‘UPDATE,products,status’, 3
— look for the three words listed and sort base name asc, and exclude a single word:
EXEC sp_search ‘UPDATE,products,status’, 2, ‘customerid’
[/cc]

That’s it!