Technical Article

Script to Find String in Database object

,

Ever wonder which stored procedures reference a particular table, or where the column "Foo" is used? This script will help you answer that.

Change the value of @Search to the string you want to find, then run the script. It will return a list of object names and types where that string is used.

Enjoy!

 

DECLARE @Search VARCHAR(255)

SET @Search = 'myData'  -- change 'myData' to the string you want to locate

SELECT DISTINCT o.[name] AS Object_Name
,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id = o.object_id
WHERE m.[definition] LIKE '%' + @Search + '%'
ORDER BY 2
,1

Rate

4 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (5)

You rated this post out of 5. Change rating