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

Page History: Disable Every Foreign Key - SQL Server

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: Thu, Jul 09, 2009, 3:20 PM


This page is part of the Foreign-Key Pages collection for SQL Server.

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, while the second set will re-enable every foreign key in the database.

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-2024, Patrick Jasinski.