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” ( 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



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook