SQL: Simple Search of Stored Procedure Code

Once upon a time I used to send out a "Tip of the Day" to my esteemed colleagues.  Here's a piece of code I used a lot prior to creation of my Data Dictionary system function (prior to me starting SharePoint development, which has pretty much precluded any SQL work).

 

Date: Tue, 22 Nov 2005 18:47:41 -0400
Subject: Tip of the Day: How to locate a word in sql

Say you need to find where a table field named 'InitSalesID' is referenced in your stored procedures and functions.

Easy, use my FindText query:

DECLARE @query varchar(100)

--Change this

SET @query = 'InitSalesID'

--End changes

--Leave this alone

SELECT DISTINCT name, type

FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id

WHERE text LIKE '%' + @query + '%'

ORDER BY name

But what if the database also contains a field called ‘DefInitSalesID’? The above query would also return those references.  How do you limit the results to those only referencing the ‘InitSalesID’ field?

You can’t simply preface the text in the query with a space, since you may have code like ‘bu.InitSalesID’, or the field name may be at the beginning of a line, etc.

The solution is this:

DECLARE @query varchar(100)

--Change this

SET @query = 'InitSalesID'

--End changes

--Leave this alone

SELECT DISTINCT name, type

FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id

WHERE text LIKE '%[^a-z]' + @query + '[^a-z]%'

ORDER BY name

By adding the [^a-z] wildcards to the like statement, you limit the search to entries that do NOT have an alpha character immediately before or after the query text.