Stored Procedures and table references

Question

Hi

I am going through a maintenance schedule to remove obsolete objects from a development SQL Server.  I have labelled several tables for removal.  What I would now like is a list of stored procedures, functions, views etc that use these tables so I can take a look and action accordingly.  How would you get a list of objects associated with removed tables please?

Thanks.

Answer 1

Check this article which explains different method of doing what you want.

http://www.mssqltips.com/sqlservertip/1294/listing-sql-server-object-dependencies/

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 2

You can get list of Views, SP, functions which use your table by executing below SQL:

EXEC sp_depends @objname = N'dbo.MyTable' ;

To get the more details and more options, you can use the URL suggested by Ashwin.

Answer 3

SELECT DISTINCT 
 O.name as SP_Name ,
 OO.name as Table_Name ,o.xtype
FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON 
O.id = D.id
INNER JOIN sys.sysobjects OO ON
OO.id = D.depid
WHERE O.xtype in ( 'P','TR','FN','TF','V ')
--and o.name in ('Your table names here ')

---

vishnu dalwadi

Answer 4

Here is a blog on the topic of object dependency:

http://www.sqlusa.com/bestpractices2008/object-dependency/

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012