Dropping table in SQL with Constraint

Question

I'm trying to drop a table in Northwind, and I'm getting:

ALTER TABLE DROP COLUMN Region failed because one or more objects access this column.

I'm using:

use [NorthWind]
go

alter table dbo.Customers
drop column Region

I guess it's because there is a constraint on the column Region. How do I find out which constraint I need to remove?

score 1 · Answer 1 · answered Dec 09 '13 at 09:36

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

OR

ALTER TABLE table_Name NOCHECK CONSTRAINT all

OR

ALTER TABLE table_Name NOCHECK CONSTRAINT constraint_name

Then try with your SQL.

Please use this when you really want to drop constraints no matter other tables affected.

If disabling the constraints is not enough, you will have to drop the constraints.

score 1 · Answer 2 · edited May 23 '17 at 10:27

1

You must drop Constraint first and then drop the table.If you have SQL Server Management Studio you can select the constraint and delete it using GUI. Or you can use command line to drop your constraints

How to remove foreign key constraint in sql server?. Taken from this answer

ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <FOREIGN_KEY_NAME>

edited May 23 '17 at 10:27

Community

1
1

answered Dec 09 '13 at 09:37

TheTechGuy

16,560
16
115
136

1

I dont know the constraint name – Dimo Dec 09 '13 at 09:42
Use the query on this [post](http://bhaveshgpatel.wordpress.com/2009/11/04/sql-server-list-all-constraints-of-database-or-table/) to display all contrainsts – TheTechGuy Dec 09 '13 at 13:22

score 0 · Answer 3 · answered Dec 09 '13 at 09:42

0

If you want to go via Sql Server Management Studio on the object explorer window, right click on the object you want to drop,then click view dependencies.

answered Dec 09 '13 at 09:42

vhadalgi

7,027
6
38
67

score -1 · Answer 4 · answered Nov 08 '16 at 16:18

First, you should know the ForeignKey - Constraint. After that, you must drop it.

Something like this:

-- looking for the Constraint's name related the dbo.Customers table
SELECT Table_Name,Constraint_Name
FROM Information_Schema.CONSTRAINT_TABLE_USAGE
WHERE Table_Name 'Customers'

-- Drop/Delete the founded constraint
ALTER TABLE [dbo].[Customers] DROP CONSTRAINT [FOREIGN_KEY_NAME]

Then, you'll run your actual script.

Dropping table in SQL with Constraint

4 Answers4