Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Disable Every Foreign Key - SQL Server

RSS
Modified on Tue, Feb 25, 2014, 11:55 AM by Administrator Categorized as SQL Server
This page is part of the Foreign Key Pages collection.
Click the icon to see the index.

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. (Thanks to Brian Coleman for this script!)

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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.