Drop all foreign keys in a table
13,540
Solution 1
There an easier method. Why all that? The script:
select ' alter table ' + schema_name(Schema_id)+'.'+ object_name(parent_object_id)
+ ' DROP CONSTRAINT ' + name from sys.foreign_keys f1
Will script the drop for all foreign keys
Solution 2
I noticed that you did not filter for foreign keys in your original query. In addition, you cannot filter the INFORMATION_SCHEMA views on database name as they will always return the current database. Instead try something like this:
EDIT
From the comments, you said that you are trying to remove all foreign keys on a given table and all foreign keys that point to that same table so that you can drop the table. I have adjusted the routine to do just that. I would suggest you adjust the question to reflect that. It should be noted that all that is really needed is to drop foreign keys that point to the table in question (the second query in the union) as any constraints on the table itself will be dropped when the table is dropped.
Declare @Database nvarchar(128)
Declare @ConstraintName nvarchar(128)
Declare @TableName nvarchar(128)
Declare @BaseSql nvarchar(max)
Declare @Sql nvarchar(max)
Declare @Tables Cursor
Set @Database = 'dotnetnuke'
Set @TableName = 'tabs'
Set @BaseSQL = 'Use DATABASENAME; Alter Table TABLENAME Drop Constraint CONSTRAINTNAME'
Set @Tables = Cursor Fast_Forward For
Select TABLE_NAME, CONSTRAINT_NAME
From INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Where CONSTRAINT_CATALOG = @Database
And TABLE_NAME = @TableName
And CONSTRAINT_TYPE = 'FOREIGN KEY'
Union All
Select FK.TABLE_NAME, RC.CONSTRAINT_NAME
From INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS As RC
Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As TC
On TC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
And TC.TABLE_NAME = @TableName
Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As FK
On FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
Open @Tables
Fetch Next From @Tables Into @TableName, @ConstraintName
While @@Fetch_Status = 0
Begin
Set @Sql = Replace(@BaseSql, 'DATABASENAME', Quotename(@Database))
Set @Sql = Replace(@Sql, 'TABLENAME', Quotename(@TableName))
Set @Sql = Replace(@Sql, 'CONSTRAINTNAME', Quotename(@ConstraintName))
Exec(@Sql)
Fetch Next From @Tables Into @TableName, @ConstraintName
End
Close @Tables
Deallocate @Tables
Solution 3
This is quite old post but might help someone.
Taking the core of the code from this post of John Paul Cook: Script to create all foreign keys
I've modified it a little bit the code, so that we can filter by table (variable @TARGET_TABLE) and schema (variable @TARGET_SCHEMA) names.
I had to modify the script so that it also prints the scripts of those foreign-keys, owned by other tables, referencing the @TARGET_TABLE.
You have to set the following three variables that are at the beginning of the script:
- @TARGET : set it to 'DROP' or 'CREATE' whether you want to generate scripts for creating the FKs or to delete them.
- @TARGET_TABLE : the name of the table (the current selected database is to be used).
- @TARGET_SCHEMA : then name of the schema that owns the table.
REMARKS:
- It also scripts dependent foreign-keys owned by other tables referencing the @TARGET_TABLE table
- Script generated does not contain [USE SomeDatabase] statement
The script looks like this:
-- User variables
DECLARE @TARGET AS VARCHAR(10); -- SET to 'DROP' or 'CREATE'
DECLARE @TARGET_TABLE AS SYSNAME; -- TABLE WHOSE FOREIGN-KEY WILL BE SCRIPTED
DECLARE @TARGET_SCHEMA AS SYSNAME; -- SCHEMA OF THE TABLE
SET @TARGET = 'DROP';
SET @TARGET_SCHEMA = 'dbo';
SET @TARGET_TABLE = 'tabs';
-- Other variables
DECLARE @schema_name sysname;
DECLARE @table_name sysname;
DECLARE @constraint_name sysname;
DECLARE @constraint_object_id int;
DECLARE @referenced_object_name sysname;
DECLARE @is_disabled bit;
DECLARE @is_not_for_replication bit;
DECLARE @is_not_trusted bit;
DECLARE @delete_referential_action tinyint;
DECLARE @update_referential_action tinyint;
DECLARE @tsql nvarchar(4000);
DECLARE @tsql2 nvarchar(4000);
DECLARE @fkCol sysname;
DECLARE @pkCol sysname;
DECLARE @col1 bit;
DECLARE FKcursor CURSOR FOR
(
SELECT
OBJECT_SCHEMA_NAME(parent_object_id)
, OBJECT_NAME(parent_object_id)
, name
, OBJECT_NAME(referenced_object_id)
, object_id
, is_disabled, is_not_for_replication, is_not_trusted
, delete_referential_action, update_referential_action
FROM
sys.foreign_keys
WHERE
OBJECT_NAME(parent_object_id) = @TARGET_TABLE
AND
OBJECT_SCHEMA_NAME(parent_object_id) = @TARGET_SCHEMA
)
UNION ALL
(
SELECT
OBJECT_SCHEMA_NAME(parent_object_id)
, OBJECT_NAME(parent_object_id)
, name
, OBJECT_NAME(referenced_object_id)
, object_id
, is_disabled, is_not_for_replication, is_not_trusted
, delete_referential_action, update_referential_action
FROM
sys.foreign_keys
WHERE
OBJECT_NAME(referenced_object_id) = @TARGET_TABLE
AND
OBJECT_SCHEMA_NAME(parent_object_id) = @TARGET_SCHEMA
)
ORDER BY 1,2;
OPEN FKcursor;
FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name
, @referenced_object_name, @constraint_object_id
, @is_disabled, @is_not_for_replication, @is_not_trusted
, @delete_referential_action, @update_referential_action;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @TARGET <> 'CREATE'
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
+ ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name) + ';';
ELSE
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
+ CASE @is_not_trusted
WHEN 0 THEN ' WITH CHECK '
ELSE ' WITH NOCHECK '
END
+ ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name)
+ ' FOREIGN KEY ('
SET @tsql2 = '';
DECLARE ColumnCursor CURSOR FOR
select COL_NAME(fk.parent_object_id, fkc.parent_column_id)
, COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc
on fk.object_id = fkc.constraint_object_id
where fkc.constraint_object_id = @constraint_object_id
order by fkc.constraint_column_id;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0
ELSE
BEGIN
SET @tsql = @tsql + ',';
SET @tsql2 = @tsql2 + ',';
END;
SET @tsql = @tsql + QUOTENAME(@fkCol);
SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);
FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
SET @tsql = @tsql + ' ) REFERENCES ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@referenced_object_name)
+ ' (' + @tsql2 + ')';
SET @tsql = @tsql
+ ' ON UPDATE ' + CASE @update_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+ ' ON DELETE ' + CASE @delete_referential_action
WHEN 0 THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE '
WHEN 2 THEN 'SET NULL '
ELSE 'SET DEFAULT '
END
+ CASE @is_not_for_replication
WHEN 1 THEN ' NOT FOR REPLICATION '
ELSE ''
END
+ ';';
END;
PRINT @tsql;
IF @TARGET = 'CREATE'
BEGIN
SET @tsql = 'ALTER TABLE '
+ QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
+ CASE @is_disabled
WHEN 0 THEN ' CHECK '
ELSE ' NOCHECK '
END
+ 'CONSTRAINT ' + QUOTENAME(@constraint_name)
+ ';';
PRINT @tsql;
END;
FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name
, @referenced_object_name, @constraint_object_id
, @is_disabled, @is_not_for_replication, @is_not_trusted
, @delete_referential_action, @update_referential_action;
END;
CLOSE FKcursor;
DEALLOCATE FKcursor;
Author by
trnTash
Updated on June 04, 2022Comments
-
trnTash about 2 years
I had this script which worked in sql server 2005
-- 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 ENDIt does not work in SQL Server 2008. How can I easily drop all foreign key constraints for a certain table? Does anyone have a better script?