Well one script to find them anyway… I wanted to share a TSQL script that I use ALL THE TIME to quickly and easily find specific text in the names or code of all objects in a database. This script has countless uses, but what I use it for most often is to track down certain tables, views, or stored procedures, that are referenced in other objects.
For example, If I have been making updates to a particular view and I’m pretty sure those updates are going to break a number of views, stored procedures, etc, I will enter the name of the view I am updating as the parameter. This script will return any object that contains that text so I can review each and make any additional needed changes to ensure reports or applications continue to run smoothly.
The script looks for text in all names of objects as well as looking at the code for objects such as views, stored procs, functions, triggers, etc. Pretty slick. I hope you find it helpful.
SET@SearchText = '%EnterTextHere%'
SELECT DISTINCT OBJECT_NAME(id) AS ObjectName,type_desc AS ObjectType,'In Script' ASCategory
FROM SYSCOMMENTS S
INNER JOINSYS.OBJECTS O ON O.Object_Id = S.id
WHERE S.text LIKE @SearchText
--In Object Name
SELECT Name AS ObjectName,type_desc AS ObjectType,'In Object Name' ASCategory
WHERE name LIKE @SearchText
SELECT O.name AS ObjectName, O.Type_Desc AS ObjectType, 'In Column: ' + C.name AS Category
FROM sys.all_objects O
INNER JOINsys.all_columns C ON O.object_id = C.object_id
WHERE C.name LIKE@SearchText
AND O.Type_Desc= 'USER_TABLE'
ORDER BY Category DESC, ObjectType,ObjectName