How to Find All References to an Object in a SQL Server Database

You can use the following scripts to find all affected objects that make any references to your search term.

You can either use sql_modules or syscomments to accomplish this purpose. Generally, they should give you the same results, but from time to time, I found that they may give you slightly different results and I haven’t had the chance to look closer how big the difference is. Just to keep in mind.

However, Microsoft has put a warning not to use syscomments in new development work as the feature will be removed in a future version of Microsoft SQL Server.

Using sql_modules:

SELECT DISTINCT OBJECT_NAME(m.object_id) AS "object name", m.*
FROM sys.sql_modules m
WHERE m.definition LIKE '%SearchTerm%'

Using syscomments:

SELECT DISTINCT OBJECT_NAME(c.id) AS "object name", c.*
FROM sys.syscomments c
WHERE c.text LIKE '%SearchTerm%'

sys.sql_modules

Returns a row for each object that is an SQL language-defined module in SQL Server, including natively compiled scalar user-defined function. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view.

sys.syscomments

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.

Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use sys.sql_modules instead. For more information, see sys.sql_modules (Transact-SQL).

Further Reading

How to Search for a String in All Tables in a Database
sys.sql_modules (Transact-SQL)
sys.syscomments (Transact-SQL)
Find all references to an object in an SQL Server database
How to Find a Column Name in SQL Server Database
How to Get Names of All Tables in SQL Server
How to Find a String in SQL Server Stored Procedures