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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

doesn´t work.

share|improve this question
up vote 5 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, provides this as an example:

-- SQL disable all constraints - disable all constraints sql server

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

Then if you like PowerShell you could do something like this (forum located on, 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: (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


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.