One script to rule them all, one script to find them – By Scott Ashby

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

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s