Page History: Disable Every Foreign Key - SQL Server

Compare Page Revisions

2 1 0 Current 2 1 0

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

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

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¶

• Drop Every Foreign Key
• List Every Foreign Key