Drop all foreign keys in a table

Question

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?

Answer 1

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

Answer 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