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

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

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

 

Regards

Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook