Peter Larsson Blog

Patron Saint of Lost Yaks

Search all code for specific keyword

This is an updated version for SQL 2005 and later to search all code for a specific keyword

SELECT p.RoutineName,
'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]
FROM (
SELECT OBJECT_NAME(so.ID) AS RoutineName,
(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body
FROM SYSOBJECTS AS so
WHERE so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X')
) AS p
WHERE p.Body LIKE '%YourKeyWordHere%'

The types are

C = CHECK constraint
D = Default or DEFAULT constraint
FN = Scalar function
IF = In-lined table-function
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
TF = Table function
TR = Trigger
V = View
X = Extended stored procedure

Legacy Comments


gbn
2008-03-14
re: Search all code for specific keyword
http://technet.microsoft.com/en-us/library/ms175081.aspx

A simple LIKE on the definition column could be easier... use OBJECT_NAME to get the name

Peso
2008-03-14
re: Search all code for specific keyword
Yes, but only type
P, RF, V, TR, FN, IF, TF, R and D
are shown in the sys.sql_modules.

C and X types are not shown.

Pennyk
2008-03-21
re: Search all code for specific keyword
I need to search stored procedures and functions for a certain table name, but I have sql 2000. Is there a similar script for 2000?

...also wondering what FOR XML PATH('') is doing?