2

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.

Portman
  • 31,325
  • 25
  • 80
  • 101
Ray
  • 179,335
  • 97
  • 219
  • 202

5 Answers5

3

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

Eric
  • 11,220
  • 12
  • 56
  • 98
1

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

SQLMenace
  • 128,762
  • 24
  • 200
  • 224
0

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.

glasnt
  • 2,847
  • 5
  • 33
  • 53
0

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

Mauro
  • 4,495
  • 3
  • 28
  • 56
-2

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

Almond
  • 1,533
  • 1
  • 14
  • 20
  • sp_depends is being depreciated and doesnt always provide the correct results do to various issues already identified in the link - http://sqlblog.com/blogs/denis_gobo/archive/2008/05/06/6653.aspx – Xander Dec 05 '12 at 14:14