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

List Every Foreign Key - SQL Server

RSS
Modified on Mon, Feb 24, 2014, 2:23 PM by Administrator Categorized as SQL Server
This page is part of the Foreign Key Pages collection.
Click the icon to see the index.

The following SQL will list every foreign key in the database, along with the column mappings.

SQL Server 2000

select distinct
     foreignKey    = foreignKey.name 
    ,onTable       = onTable.name
    ,againstTable  = againstTable.name 
    ,onColumn      = onColumn.name 
    ,againstColumn = againstColumn.name 

    ,enf           = case 
                     when ObjectProperty(fk.constid,'CnstIsDisabled') <> 0 
                         then '* NOCHECK *'
                     else 'enforced'
                     end

    ,del           = case
                     when ObjectProperty(fk.constid,'CnstIsDeleteCascade') = 1 
                         then 'cascade deletes'
                     else ''
                     end

    ,upd           = case
                     when ObjectProperty(fk.constid,'CnstIsUpdateCascade') = 1 
                         then 'cascade updates'
                     else ''
                     end

from 

    sysforeignkeys fk

    inner join sysobjects foreignKey  
        on foreignKey.id = fk.constid

    inner join sysobjects onTable 
        on fk.fkeyid = onTable.id

    inner join syscolumns onColumn
        on fk.fkeyid = onColumn.id
        and fk.fkey = onColumn.colid

    inner join sysobjects againstTable  
        on fk.rkeyid = againstTable.id

    inner join syscolumns againstColumn
        on fk.rkeyid = againstColumn.id
        and fk.rkey = againstColumn.colid

where 1=1
    AND againstTable.TYPE = 'U'
    AND onTable.TYPE = 'U'

order by 
    1, 2, 3

SQL Server 2005

/*==================================================================================================
    OBJECT: Fkeys view
    SOURCE: http://www.jasinskionline.com/TechnicalWiki/List-Every-Foreign-Key.ashx
==================================================================================================*/
create view dbo.Fkeys
as
with RefAction as (
    select RefCode = 0, RefDesc = 'Do nothing'
    union select 1, 'Cascade'
    union select 2, 'Set null'
    union select 3, 'Set default'
    )
,fk as (
    select
         ForeignKey         = fk.name
        ,OnSchema           = ots.name 
        ,OnTable            = OnTable.name
        ,AgainstSchema      = ats.name
        ,AgainstTable       = AgainstTable.name
        ,OnColumn           = OnColumn.name
        ,AgainstColumn      = AgainstColumn.name
        ,Enforced           = case when fk.is_disabled = 1 then 'Disabled' else 'Enforced' end
        ,DeleteAction       = da.RefDesc
        ,UpdateAction       = ua.RefDesc

    from 

        sys.foreign_keys fk

        inner join sys.foreign_key_columns fkcols
            on fk.object_id = fkcols.constraint_object_id

        inner join sys.objects OnTable
            on fk.parent_object_id = OnTable.object_id

        inner join sys.schemas ots
            on OnTable.schema_id = ots.schema_id

        inner join sys.objects AgainstTable
            on fk.referenced_object_id = AgainstTable.object_id

        inner join sys.schemas ats
            on AgainstTable.schema_id = ats.schema_id

        inner join sys.columns OnColumn
            on fkcols.parent_column_id = OnColumn.column_id
            and fkcols.parent_object_id = OnColumn.object_id

        inner join sys.columns AgainstColumn
            on fkcols.referenced_column_id = AgainstColumn.column_id
            and fkcols.referenced_object_id = AgainstColumn.object_id

        inner join RefAction da
            on fk.delete_referential_action = da.RefCode

        inner join RefAction ua
            on fk.update_referential_action = ua.RefCode
    )
select
     *
    ,DropCode   = 'alter table [' + OnSchema + '].[' + OnTable + '] drop constraint [' + ForeignKey + ']'
    ,CreateCode = 'alter table ['  + OnSchema + '].[' + OnTable + '] with check add constraint [' + ForeignKey 
                    + '] foreign key ([' + OnColumn + ']) references [' + AgainstSchema + '].[' + AgainstTable
                    + '] ([' + AgainstColumn + '])'
from 
    fk

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