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.

1_find stored procedure related to table in database

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.

2_find stored procedure related to table in database

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *