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: Drop Every Foreign Key - SQL Server

Compare Page Revisions



« Older Revision - Back to Page History - Current Revision


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


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

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