In SQL Server, how do I identify *all* dependencies for a specific table using system tables/views?

Question

I am writing a DDL script to drop a number of tables but need to identify all dependencies for those tables first. Those dependencies include foreign key constraints, stored procedures, views, etc. Preferably, I want to programmatically script out dropping those dependencies using the system tables/views before dropping the dependent table.

Answer 1

You can use the sp_depends stored procedure to do this:

USE AdventureWorks GO EXEC sp_depends @objname = N'Sales.Customer' ;

http://msdn.microsoft.com/en-us/library/ms189487(SQL.90).aspx

Answer 2

sp_depends is not reliable see: Do you depend on sp_depends (no pun intended)

Answer 3

This is extremely messy to write from scratch. Have you considered a 3rd party tool like Red-Gate SQL Dependency Tracker?

Answer 4

you could always search through the syscomments table....that might take a while though...

Answer 5

Could you reference sysreferences?

select 'if exists (select name from sysobjects where name = '''+c.name+''') '
+' alter table ' + t.name +' drop constraint '+ c.name 
 from sysreferences sbr, sysobjects c, sysobjects t, sysobjects r
 where c.id = constrid 
  and t.id = tableid 
  and reftabid = r.id
  and r.name = 'my_table'

That will generate a whole lot of conditional drop constraint calls. Should work.