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

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.

From stackoverflow Ray Vega

• 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

  From Almond

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

  From SQLMenace

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

  From Eric

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

  From Mauro

• 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.

  From glasnt