List Every Foreign Key - SQL Server

This page is part of the Foreign Key Pages collection. Click the icon to see the index.

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