[T-SQL] Drop all constraints on a table

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

Published Wednesday, April 12, 2006 5:09 AM by Jon Galloway

Filed under: SQL

Comments

# re: [T-SQL] Drop all constraints on a table

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.

Thursday, April 13, 2006 2:41 AM by Sean Price

# re: [T-SQL] Drop all constraints on a table

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

Friday, June 06, 2008 3:43 PM by Shrikant

# re: [T-SQL] Drop all constraints on a table

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)

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

Saturday, July 19, 2008 12:01 PM by Adam Turner

# re: [T-SQL] Drop all constraints on a table

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

SET ANSI_NULLS ON

ALTER PROCEDURE usp_ClearConstraints

@DatabaseName varchar(50)

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

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

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

Saturday, July 19, 2008 1:02 PM by Adam Turner

# SQL Quicklinks - sashidhar.com

Pingback from SQL Quicklinks - sashidhar.com

Friday, January 02, 2009 4:31 PM by SQL Quicklinks - sashidhar.com

# re: [T-SQL] Drop all constraints on a table

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')

ALTER PROCEDURE dbo.sp_ClearConstraints

@DatabaseName varchar(50)

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

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

Wednesday, December 09, 2009 2:41 PM by Foobar Explitive

# Removendo todas Constraints – SQL Server « Looking For…

Pingback from Removendo todas Constraints – SQL Server « Looking For…

Tuesday, January 04, 2011 4:56 AM by Removendo todas Constraints – SQL Server « Looking For…

# re: [T-SQL] Drop all constraints on a table

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

Wednesday, February 02, 2011 5:00 AM by Krunal

# re: [T-SQL] Drop all constraints on a table

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

Sunday, June 19, 2011 11:19 PM by Peter Gfader

# re: [T-SQL] Drop all constraints on a table

--Thats is the Common Code :-)

create procedure sp_dropconstraint @table varchar(255)

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

Friday, September 30, 2011 11:43 AM by Walter

# re: [T-SQL] Drop all constraints on a table

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROC [dbo].[DeleteAllFKs]

@database nvarchar(128)

,@Schema nvarchar(128)

,@table nvarchar(128)

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

Wednesday, December 14, 2011 5:24 AM by Fraser Graham

# How to change database primary key field with a foreign key constraint

How to change database primary key field with a foreign key constraint

Monday, July 02, 2012 1:04 PM by {BeltBlog}

# re: [T-SQL] Drop all constraints on a table

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

Saturday, July 28, 2012 3:38 AM by SQL Butler

# re: [T-SQL] Drop all constraints on a table

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.

Monday, April 01, 2013 9:19 PM by txnphgfcxfe@yahoo.com

# [T-SQL] Drop all constraints on a table - Jon Galloway

Proudly owning and operating a enterprise is much more than a total-time determination. It is tough, demanding conduct and calls for experienced labors. Do not soar the gun when hoping to uncover mov...

Friday, April 12, 2013 3:46 AM by HowMarks

# t-sql cript to drop all constraints | nooblikeaboss

Pingback from t-sql cript to drop all constraints | nooblikeaboss

Tuesday, May 21, 2013 7:00 AM by t-sql cript to drop all constraints | nooblikeaboss

Jon Galloway

This Blog

Syndication

Search

Tags

News

Navigation

Archives

Links