SQL Server : how to drop all constraints on a table in T-SQL

Question

I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables.

Is there any way to do that with T-SQL? I cannot find any solution and code like:

ALTER TABLE testDB.dbo.testTable1
DROP ALL CONSTRAINT
GO

doesn´t work.

Answer 1

You will have to build your T-SQL statement for each constraint that you want to drop. There are undocumented procedures that could be used for this like sp_MSforeachtable, but I am not to fond of using them. However, SQLUSA.com provides this as an example:

-- SQL disable all constraints - disable all constraints sql server
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

Another option was provided here on SO, by Aaron Bertrand.

Then if you like PowerShell you could do something like this (forum located on SQLServerCentral.com), provided by Rob Farley:

#This is one line just broken up for readability.
PS SQLSERVER:\sql\localhost\default\Databases\YourDatabase>
 dir Tables\*\Columns\*\DefaultConstraint 
| % {$_.Script(); $_.Drop()}

And if you want a few more examples check here.

Answer 2

If you have SQL Server 2000 database, the script given here should do it:

http://sradack.blogspot.com/2007/04/remove-all-tables-and-constraints-from.html (Caution: the scripts at this location will delete all your tables if ran without modification!)

If you have any later version and have schemas other than dbo, see the scripts given here:

T-SQL to drop all Constraints