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. 

USE DBName

DECLARE@SearchText VARCHAR(50)

SET@SearchText = '%EnterTextHere%'

--In Script

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

UNION

--In Object Name

SELECT Name AS ObjectName,type_desc AS ObjectType,'In Object Name' ASCategory

FROM sys.all_objects

WHERE name LIKE @SearchText

UNION

--Table Columns

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