SQL Server 2005, need script to check for all constraints on table

Question

I continue to get this error:

Object '%s' cannot be renamed because the object participates in enforced dependencies

I need to find a script that will help me to find all the dependencies that there is with this table. I will need to drop them, rename, then bring them back.

Thanks.

SQL Server 2005

Answer 1

To disable all contraints

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"
go

To Re-enable

sp_msforeachtable"ALTER TABLE ? CHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER  all"
go

You should easily be able to adapt this to just one table.

Answer 2

This posting contains a db reverse engineer script that I originally wrote for SQL Server 2000 and ported to 2005. If you look at the bit that does foreign key relationships you can see how to get the dependencies from the data dictionary.