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

Drop Every Foreign Key - SQL Server

RSS
Modified on Wed, Dec 02, 2009, 7:38 AM by Administrator Categorized as SQL Server
This page is part of the Foreign Key Pages collection.
Click the icon to see the index.

When executed against the database of interest, the following T-SQL code will generate a set of SQL statements which, when executed against the same database, will drop every foreign key in the database.

select distinct
    sql = 'alter table [' + onTable.name + '] drop 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'

order by 
    1

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