Drop all foreign keys in a table

问题:

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
END

It 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?

回答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

回答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

回答3:

The script as written assumes that you are running it in the database containing the object to be dropped. Are you running it from another database (such as master or tempdb)? Perhaps your default (on login) database changed?

(I also noticed when trying it on one of my DBs that it doesn't factor in schemas. This might be worth adding in some time.)

回答4:

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:

1. It also scripts dependent foreign-keys owned by other tables referencing the @TARGET_TABLE table
2. 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;

回答5:

[T-SQL] Drop all constraints on a table

http://weblogs.asp.net/jgalloway/archive/2006/04/12/442616.aspx

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