Jon Galloway

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.

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

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

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

Pingback from  SQL Quicklinks - sashidhar.com