Search for text within Stored Procedure, View, Trigger and Function code

Was helping out a couple of colleagues to find a little piece of code from a plethora of stored procedures and triggers in the morning. Hence, thought of posting it here as part of my very infrequent Not-So-Hip-Snippets-Yet-Useful-At-Times series of code snippets.

This simple little snippet searched for pieces of text within a database object such as a stored procedure or trigger, and returns the name, type and last modified date of the object. You could search for pieces of text such as UPDATE dbo.Monsters or SET [Gender] = or some piece of code that you remember writing inside an SP or so.

1 DECLARE @SearchPhrase varchar(100) 2 DECLARE @Types TABLE ([type] varchar(5) COLLATE Latin1_General_CI_AS_KS_WS) 3 4 -- ENTER TEXT TO SEARCH FOR: 5 SET @SearchPhrase = '' 6 7 -- The following statements include object types to be searched for. 8 -- COMMENT OUT OBJECT TYPES THAT YOU DO NOT WISH TO BE INDLUDED IN THE SEARCH. 9 INSERT INTO @Types 10 SELECT '' 11 UNION SELECT 'TR'-- Triggers 12 UNION SELECT 'P'-- Stored Procedures 13 UNION SELECT 'FN'-- Scalar Functions 14 UNION SELECT 'V'-- Views 15 16 /* Search code */ 17 SELECT 18 O.[name] AS [ObjectName], 19 O.[type_desc] AS [ObjectType], 20 O.[modify_date] AS [ModifiedDate] 21 FROM 22 sys.syscomments C 23 INNER JOIN sys.objects O 24 ON C.[id] = O.[object_id] 25 WHERE 26 C.[text] LIKE '%' + @SearchPhrase + '%' 27 AND O.[type] IN (SELECT [type] FROM @Types)

It’s a great way to search for a database object that you had written and cannot remember anymore, save for the little bit of code that you remember writing in it.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s