How to drop table with constraint in Microsoft SQL?

Question

How can i drop tables with constraints,

use my_db0


if exists(select* from sys.tables where name='Tbl1')
drop table Tbl1 --cascade constraints;


Create table Tbl1(
nameID int primary key
)


if exists (select* from sys.tables where name='Tbl2')
drop table Tbl2


Create table Tbl2(
lastNameID int primary key,
nameID int foreign key references Tbl1(nameID)
)

Possible duplicate of [SQL DROP TABLE foreign key constraint](http://stackoverflow.com/questions/1776079/sql-drop-table-foreign-key-constraint) — Peter, Nov 02 '15 at 08:54

Julien Vavasseur · Answer 1 · 2015-11-02T09:12:21.577

You can use this query:

Select Query = 
    'If  EXISTS (Select * FROM sys.foreign_keys Where ' 
        + ' object_id = OBJECT_ID(N''' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(fk.name) + ''')'
        + ' And parent_object_id = OBJECT_ID(N''' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + OBJECT_NAME(fk.parent_object_id) + ''')) ' +

    'ALTER TABLE ' +  QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) 
        + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) 
        + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + '; '
    , [Schema] = sh.name, [Table] = OBJECT_NAME(fk.parent_object_id), [Constraint] =  ob.name

From sys.foreign_keys as fk
Inner Join sys.objects as ob on ob.object_id = fk.parent_object_id
Inner Join sys.schemas as sh on ob.schema_id = sh.schema_id
Where ob.name in ('xxx', 'yyy');

It will output:

Drop constraint query
Schema name
Table name
Constraint name

You can then dynamicaly execute the queries in the first column:

Exec sp_executesql @sql

Where @sql comes from the Query column. Once the constraints have been remove, you can drop the table.

score 1 · Answer 2 · answered Nov 02 '15 at 09:12

The table Tbl2 contains nameID as a foreign key , so you have to first wipe the the data in Tbl2 and then drop the Tbl1

if exists (select* from sys.tables where name='Tbl2')
drop table Tbl2

if exists(select* from sys.tables where name='Tbl1')
drop table Tbl1

score 1 · Answer 3 · answered Nov 03 '15 at 12:56

In SQL Server 2016 you can use DROP IF EXISTS:

ALTER TABLE my_table
DROP CONSTRAINT IF EXISTS <name>

DROP TABLE IF EXISTS my_table

See http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016.aspx

Jovan

How to drop table with constraint in Microsoft SQL?

3 Answers3