Post a Comment On: mo.notono.us

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 thisSET @query = 'InitSalesID'--End changes --Leave this aloneSELECT DISTINCT name, typeFROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.idWHERE 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 thisSET @query = 'InitSalesID'--End changes --Leave this aloneSELECT DISTINCT name, typeFROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.idWHERE 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.