Tuesday, November 16, 2010 11:40 PM
Mikael Sand
Search all SPs for a string
Ever asked this question: “I wonder if we access this table from any stored procedures?”.
Well here is how to find out:
SELECT distinct so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%TABLE/COLUMN/string%'
Nice! So now you can search all the procedure text for a reference to a table that is about to change or all “TODO”-tags.
Commenter, and much respected blogger, Thiago Almeida would like to point out the free and useful Red Gate tool SQL-search.
Try it, you might like it. Red Gate usually makes good software.
Filed under: T-SQL, SQL Server