[T-SQL] Drop all constraints on a table

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

Foobar Explitive — Wed, 09 Dec 2009 19:41:45 GMT

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

SQL Quicklinks - sashidhar.com

SQL Quicklinks - sashidhar.com — Fri, 02 Jan 2009 21:31:59 GMT

Pingback from SQL Quicklinks - sashidhar.com

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

Adam Turner — Sat, 19 Jul 2008 17:02:48 GMT

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

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

Adam Turner — Sat, 19 Jul 2008 16:01:18 GMT

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

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

Shrikant — Fri, 06 Jun 2008 19:43:26 GMT

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

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

Sean Price — Thu, 13 Apr 2006 06:41:00 GMT

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.