SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

SQL Server – List of Tables Used in Stored Procedure

Posted by Prashant on December 1, 2012

Today while developing a piece of logic in our development, my colleague said that I know there is a procedure which has same kind of logic and that may help us building the logic quickly. But the question was how to find that procedure !!!

Then the idea came up was if we can find the list of procedures that are dependable on Table1 & Table2 ?

…and that inspired me to write this post.

We spend some time researching system tables and here is the findings to find list of tables used in a procedure.

;WITH procs
SELECT o1.name AS proc_name,
o2.name AS table_name,
ROW_NUMBER() OVER(PARTITION BY o1.name,o2.name ORDER BY o1.name,o2.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o1
ON o1.id=d.id
INNER JOIN sysobjects o2
ON o2.id=d.depid
WHERE o1.xtype = 'P'
--AND o2.name = 'tabname1' OR o2.name = 'tblname2'
SELECT proc_name, table_name
FROM procs
WHERE row = 1
ORDER BY proc_name, table_name

Let me know if you have any better idea for this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: