[T-SQL] Drop all constraints on a table

Wednesday, April 12, 2006

SQL

Here's a simple scriptlet I've included in database upgrade scripts to drop all constraints on a table which will then allow dropping the table. Of course, you'll want to replace the @database and @table parameters.

This doesn't take care of circumstances where another table has a foreign key to the table you want to drop - in that case, you'll want to explicity drop that foreign key first.

-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = 'dotnetnuke'
set @table = 'tabs'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    where    constraint_catalog = @database and
            table_name = @table
    exec    sp_executesql @sql
END

11 Comments

Something like this would work to drop all the FK constraints off the table.

DECLARE @database nvarchar(50)

DECLARE @table nvarchar(50)

set @database = 'dotnetnuke'

set @table = 'tabs'

declare @schema nvarchar(128), @tbl nvarchar(128), @constraint nvarchar(128)

DECLARE @sql nvarchar(255)

declare cur cursor fast_forward for

select distinct cu.constraint_schema, cu.table_name, cu.constraint_name

from information_schema.table_constraints tc

join information_schema.referential_constraints rc on rc.unique_constraint_name = tc.constraint_name

join information_schema.constraint_column_usage cu on cu.constraint_name = rc.constraint_name

where tc.constraint_catalog = @database and tc.table_name = @table

open cur

fetch next from cur into @schema, @tbl, @constraint

while @@fetch_status <> -1

begin

select @sql = 'ALTER TABLE ' + @schema + '.' + @tbl + ' DROP CONSTRAINT ' + @constraint

exec sp_executesql @sql

fetch next from cur into @schema, @tbl, @constraint

end

close cur

deallocate cur

You could UNION your query onto the above and do both queries in one shot. Might run into some RI errors depending on the order things got deleted though.

Sean Price - Wednesday, April 12, 2006 11:41:00 PM

Thanks Sean for your script. Worked flawlessly. I was about to put my coding cap on, but your script fixed my problem. Thanks.

Shrikant - Friday, June 6, 2008 12:43:26 PM

Here's some code that will go a step further and drop the tables as well as check to be sure you're not on the production server"

CREATE PROCEDURE usp_ClearConstraints
@DatabaseName varchar(50)
AS

IF @@ServerName 'YourProductionServerName'
BEGIN
DECLARE @TableName NVARCHAR(50)
DECLARE @ConstraintName NVARCHAR(50)

DECLARE Constraints CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
JOIN SYSOBJECTS s
ON u.TABLE_NAME = s.Name
WHERE TABLE_CATALOG = @DatabaseName
AND s.xType = 'U'

OPEN Constraints
FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('USE ' + @DatabaseName + ' ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']')
FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName
END

CLOSE Constraints
DEALLOCATE Constraints

DECLARE Tables CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES u
JOIN SYSOBJECTS s
ON u.TABLE_NAME = s.Name
WHERE TABLE_CATALOG = @DatabaseName
AND s.xType = 'U'

OPEN Tables
FETCH NEXT FROM Tables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('USE ' + @DatabaseName + ' DROP TABLE [' + @TableName + ']')
FETCH NEXT FROM Tables INTO @TableName

END

CLOSE Tables
DEALLOCATE Tables

END

GO

Adam Turner - Saturday, July 19, 2008 9:01:18 AM

Sorry didn't realize it didn't work with FK constraints...so here's the full code that will drop FK constraints and the tables and make sure you're not running on the production server:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE usp_ClearConstraints
@DatabaseName varchar(50)
AS

IF @@ServerName 'YourProductionServer'
BEGIN
DECLARE @TableName NVARCHAR(50)
DECLARE @ConstraintName NVARCHAR(50)

DECLARE Constraints CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
JOIN SYSOBJECTS s
ON u.TABLE_NAME = s.Name
WHERE TABLE_CATALOG = @DatabaseName
AND s.xType = 'U'
ORDER BY u.Constraint_Name --Get FK constraints first

OPEN Constraints
FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName

WHILE @@FETCH_STATUS = 0
BEGIN

IF LEFT(@ConstraintName, 2) = 'FK'
BEGIN
DECLARE @FKTableName as varchar(50)
DECLARE @FKName as varchar(50)
SET @FKName = (SELECT TABLE_CATALOG FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_CATALOG = @TableName)
SET @FKTableName = (SELECT object_name(fkeyid) from sysreferences where object_name(constid) = @FKName)
EXEC('USE ' + @DatabaseName + ' ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']')
FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName
END

ELSE --Else if there are no foreign key constraints on the table
BEGIN
EXEC('USE ' + @DatabaseName + ' ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']')
FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName
END
END

CLOSE Constraints
DEALLOCATE Constraints

DECLARE Tables CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES u
JOIN SYSOBJECTS s
ON u.TABLE_NAME = s.Name
WHERE TABLE_CATALOG = @DatabaseName
AND s.xType = 'U'

OPEN Tables
FETCH NEXT FROM Tables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('USE ' + @DatabaseName + ' DROP TABLE [' + @TableName + ']')
FETCH NEXT FROM Tables INTO @TableName

END

CLOSE Tables
DEALLOCATE Tables

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Adam Turner - Saturday, July 19, 2008 10:02:48 AM

This worked better for me. SQL 2008:

if object_id('dbo.sp_ClearConstraints') is null
exec ('Create proc dbo.sp_ClearConstraints as begin print '''' end')

GO

go
ALTER PROCEDURE dbo.sp_ClearConstraints
@DatabaseName varchar(50)
AS
BEGIN

DECLARE @TableName sysname
DECLARE @ConstraintName sysname
DECLARE @FKTableName as sysname
DECLARE @FKName as sysname
DECLARE @Schema as sysname
DECLARE @ConstraintType as sysname

DECLARE @SQL varchar(2000)

DECLARE Constraints CURSOR FOR
with KeyInfo as (
select Tablename = OBJECT_NAME(kc.parent_object_id), KeyName = kc.name, TableSchema = SCHEMA_NAME(so.schema_id), [type] = kc.type, [order] = 999
from sys.key_constraints kc
join sys.objects so on kc.parent_object_id = so.[object_id]
union
select OBJECT_NAME(kc.parent_object_id), kc.name, SCHEMA_NAME(so.schema_id), 'FK', [order] = 0
from sys.foreign_keys kc
join sys.objects so on kc.parent_object_id = so.[object_id]
)
select TableName, KeyName, TableSchema, [Type]
from keyinfo
order by [order]

OPEN Constraints

FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName, @Schema, @ConstraintType

WHILE @@FETCH_STATUS = 0
BEGIN
IF @ConstraintType = 'FK'
BEGIN

SET @SQL = 'USE ' + @DatabaseName + ' ALTER TABLE [' + @Schema + '].[' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']'

PRINT @SQL

EXEC (@SQL)

END
ELSE --Else if there are no foreign key constraints on the table
BEGIN

SET @SQL = 'USE ' + @DatabaseName + ' ALTER TABLE [' + @Schema + '].[' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']'

PRINT @SQL

EXEC (@SQL)

END

FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName, @Schema, @ConstraintType
END

CLOSE Constraints
DEALLOCATE Constraints

END

GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

Foobar Explitive - Wednesday, December 9, 2009 11:41:45 AM

Nice Sripts....It helps a lot...

Krunal - Wednesday, February 2, 2011 2:00:58 AM

Using "INFORMATION_SCHEMA.TABLE_CONSTRAINTS" didn't work for us (SQL Server 2008)
We are using "sys.objects" and that works better...

Yours fixed below

-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SELECT @database = DB_NAME()
set @table = 'AuctionLotExt'

DECLARE @sql nvarchar(255)
WHILE EXISTS(SELECT OBJECT_NAME(OBJECT_ID) AS CONSTRAINT_NAME FROM sys.objects WHERE type_desc LIKE 'DEFAULT_CONSTRAINT' AND OBJECT_NAME(parent_object_id) = @table)
BEGIN
SELECT @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + OBJECT_NAME(OBJECT_ID)
FROM sys.objects WHERE type_desc LIKE 'DEFAULT_CONSTRAINT' AND OBJECT_NAME(parent_object_id) = @table
exec sp_executesql @sql
END
GO

Peter Gfader - Sunday, June 19, 2011 8:19:18 PM

--Thats is the Common Code :-)

create procedure sp_dropconstraint @table varchar(255)
as

declare @nr int
declare @c varchar(255)
declare @t varchar(255)
declare @sql varchar(255)

DECLARE c1 CURSOR FOR

select 2,CONSTRAINT_NAME,table_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table
union
select 1,k.Name,s2.name from sys.foreign_keys k
inner join dbo.sysobjects s on s.id = k.referenced_object_id
inner join dbo.sysobjects s2 on s2.id = k.[parent_object_id]
where s.name=@table
order by 1

OPEN c1
FETCH NEXT FROM c1 INTO @nr,@c ,@t

WHILE @@FETCH_STATUS = 0
BEGIN

select @sql = 'ALTER TABLE ' + @t + ' DROP CONSTRAINT ' + @c
print @sql
exec sp_executesql @sql

FETCH NEXT FROM c1 INTO @nr,@c,@t

END

CLOSE c1
DEALLOCATE c1

go

Walter - Friday, September 30, 2011 8:43:08 AM

This worked for me to delete all constraints against a table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DeleteAllFKs]
@database nvarchar(128)
,@Schema nvarchar(128)
,@table nvarchar(128)
AS

BEGIN

declare @tbl nvarchar(128), @constraint nvarchar(128), @sql nvarchar(255)

declare cur cursor fast_forward for
select distinct
cu.table_name
, cu.constraint_name
from
information_schema.table_constraints tc
join information_schema.referential_constraints rc on rc.unique_constraint_name = tc.constraint_name
join information_schema.constraint_column_usage cu on cu.constraint_name = rc.constraint_name
where
tc.constraint_catalog = @database and tc.table_name = @table and cu.CONSTRAINT_SCHEMA = @Schema

open cur

fetch next from cur into @tbl, @constraint

while @@fetch_status -1
begin
select @sql = 'ALTER TABLE ' + @schema + '.' + @tbl + ' DROP CONSTRAINT ' + @constraint

exec sp_executesql @sql

fetch next from cur into @schema, @tbl, @constraint

end

close cur
deallocate cur

END

Fraser Graham - Wednesday, December 14, 2011 2:24:26 AM

the SP by Foobar Explitive worked ´like dream for me on SQL2008 R2 - thx mate

SQL Butler - Saturday, July 28, 2012 12:38:19 AM

Hello there! I simply want to offer you a huge thumbs up for the great info you have right here on this post. I am coming back to your site for more soon.

txnphgfcxfe@yahoo.com - Monday, April 1, 2013 6:19:07 PM

Comments have been disabled for this content.