Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.