Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

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

with RefAction as (
    select RefCode = 0, RefDesc = 'Do nothing'
    union select 1, 'Cascade'
    union select 2, 'Set null'
    union select 3, 'Set default'
    )
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