I'm writing a DDL script to decrease numerous tables but have to identify all dependencies for individuals tables first. Individuals dependencies include foreign key constraints, saved methods, sights, etc. Ideally, I wish to programmatically script out shedding individuals dependencies while using system tables/sights before shedding the dependent table.
This really is very untidy to create on your own. Have you thought about a third party tool like Red-Gate SQL Dependency Tracker?
sp_is dependent isn't reliable see: Do you depend on sp_depends (no pun intended)
you can always sort through the syscomments table....that may require sometime though...
Would you reference
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'
Which will generate a great deal of conditional
drop constraint calls. Should work.
You should use the sp_is dependent saved procedure to get this done:
EXEC sp_depends @objname = N'Sales.Customer' ;