SQL Server : how to drop all constraints on a table in T-SQL 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.
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.