Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Print | posted on Friday, March 14, 2008 2:05 PM | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Feedback

Gravatar

# 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
3/14/2008 4:38 PM | gbn
Gravatar

# 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.
3/14/2008 4:55 PM | Peso
Gravatar

# re: Search all code for specific keyword

You should either use the OBJECT_DEFINITION() function (with sysobjects or whatever) or query sys.sql_modules + sys.default_constraints + sys.check_constraints

Works for C,D,P,FN,R,RF,TR,IF,TF,V.

extended stored procedure code is not accessible anyway
3/18/2008 1:48 PM | Saggi Neumann
Gravatar

# 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?
3/21/2008 7:45 PM | Pennyk
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET