I just re-discovered this little script in my “Magic SQL” folder, and thought I’d share it.
Below is a script to search the definition of all stored procedures in a database for a specified value – useful in circumstances when you’re trying to figure out if a particular column is used anywhere in procedure code, or something like that.
USE DatabaseName DECLARE @SearchString VARCHAR(500) SET @SearchString = 'Search' SELECT DISTINCT Name FROM sysobjects SO JOIN syscomments SC ON SO.id = SC.id WHERE SO.[type] = 'P' AND SO.category = 0 AND SC.[text] LIKE '%' + @SearchString + '%'
To use the script, change the USE statement to point at the database you’d like to search, and change the value of @SearchString to the value you’d like to search for.
The result set will provide the name of any procedures containing the value defined within @SearchString.