Page History: Disable Every Foreign Key - SQL Server

Compare Page Revisions

2 1 0 Current 2 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Tue, Feb 25, 2014, 11:53 AM

Unconditional Disabling¶

The following script will disable and enable all constraints (including foreign keys) in the current database. Note that ALL the constraints are enabled by the second statement, regardless of whether they were initially disabled.

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- enable all constraints
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Selective Disabling¶

The following T-SQL code will generate a set of SQL statements. When executed against the database of interest, the first set will disable every foreign key in the database that's enabled, while the second set will re-enable every foreign key in the database that was disabled by the first set of SQL statements.

SQL Server 2005¶

-- This SQL generates a set of SQL statements to disable every foreign 
-- key in the database
select distinct
    sql = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name + 
            '] NOCHECK CONSTRAINT [' + foreignKey.name + ']'

from 

    sysforeignkeys fk

    inner join sysobjects foreignKey  
        on foreignKey.id = fk.constid

    inner join sys.objects onTable 
        on fk.fkeyid = onTable.object_id

    inner join sys.schemas onSchema
        on onSchema.schema_id = onTable.schema_id

    inner join sysobjects againstTable  
        on fk.rkeyid = againstTable.id

where 1=1
    AND againstTable.TYPE = 'U'
    AND onTable.TYPE = 'U'
    and ObjectProperty(fk.constid,'CnstIsDisabled') = 0
    and onTable.name not like 'z[_]DeadCode[_]%'
    and onTable.name not like 'aspnet%'

order by 
    1

-- This SQL generates a set of SQL statements to re-enable every foreign 
-- key in the database
select distinct
    sql = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name + 
            '] CHECK CONSTRAINT [' + foreignKey.name + ']'

from 

    sysforeignkeys fk

    inner join sysobjects foreignKey  
        on foreignKey.id = fk.constid

    inner join sys.objects onTable 
        on fk.fkeyid = onTable.object_id

    inner join sys.schemas onSchema
        on onSchema.schema_id = onTable.schema_id

    inner join sysobjects againstTable  
        on fk.rkeyid = againstTable.id

where 1=1
    AND againstTable.TYPE = 'U'
    AND onTable.TYPE = 'U'
    and ObjectProperty(fk.constid,'CnstIsDisabled') = 0
    and onTable.name not like 'z[_]DeadCode[_]%'
    and onTable.name not like 'aspnet%'

order by 
    1

SQL Server 2000¶

-- This SQL generates a set of SQL statements to disable every foreign 
-- key in the database 
select distinct
    sql = 'ALTER TABLE [' + onTable.name + '] NOCHECK CONSTRAINT [' 
            + foreignKey.name + ']'

from 

    sysforeignkeys fk

    inner join sysobjects foreignKey  
        on foreignKey.id = fk.constid

    inner join sysobjects onTable 
        on fk.fkeyid = onTable.id

    inner join sysobjects againstTable  
        on fk.rkeyid = againstTable.id

where 1=1
    AND againstTable.TYPE = 'U'
    AND onTable.TYPE = 'U'
    and ObjectProperty(fk.constid,'CnstIsDisabled') = 0
    and onTable.name not like 'z[_]DeadCode[_]%'
    and onTable.name not like 'aspnet%'

order by 
    1

-- This SQL generates a set of SQL statements to re-enable every foreign 
-- key in the database
select distinct
    sql = 'ALTER TABLE [' + onTable.name + '] CHECK CONSTRAINT [' 
            + foreignKey.name + ']'

from 

    sysforeignkeys fk

    inner join sysobjects foreignKey  
        on foreignKey.id = fk.constid

    inner join sysobjects onTable 
        on fk.fkeyid = onTable.id

    inner join sysobjects againstTable  
        on fk.rkeyid = againstTable.id

where 1=1
    AND againstTable.TYPE = 'U'
    AND onTable.TYPE = 'U'
    and ObjectProperty(fk.constid,'CnstIsDisabled') = 0
    and onTable.name not like 'z[_]DeadCode[_]%'
    and onTable.name not like 'aspnet%'

order by 
    1

See Also¶

• Drop Every Foreign Key
• List Every Foreign Key