List Every Foreign Key - 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