Find stored procedure related to table in database in SQL Server

Many a times need arises to find all stored procedures related to a table in the database. The below queries come handy then.

-- Query 1
SELECT DISTINCT objects.name, objects.type,
comments.text proc_defintion
FROM syscomments comments
INNER JOIN sys.objects objects 
ON comments.id=objects.object_id
WHERE comments.text LIKE '%Person.Address%'
AND objects.type='P'

The system table syscomments contains the definition of procedures, view, rule, default, trigger, check constraints and default constraints in a database. Thus, Querying syscomments as shown above returns the list of procedures which have a particular table in their definition.  The output of above query is shown below.

Another way to get the above result is to query sys.sql_modules catalog view. The query for the same is given below

-- Query 2
select objects.name,objects.type,
modules.definition 
 from sys.sql_modules modules
 join sys.objects objects
 on objects.object_id=modules.object_id
 WHERE modules.definition like '%Person.Address%'
 and objects.type='P'

To quote MSDN “Returns a row for each object that is an SQL language-defined module in SQL Server. 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” (http://msdn.microsoft.com/en-us/library/ms175081.aspx). Thus, querying the sys.sql_modules as shown above lists all stored procedure with a specified table in their definition. It is available from SQL Server 2008.

 
Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook