--外键信息

select fk.name fkname , ftable.name ftablename, cn.name fkcol, rtable.name ftablename,ObjectProperty(fk.id,'CnstIsUpdateCascade') updatCase,ObjectProperty(fk.id,'CnstIsDeleteCascade') deletCase from sysforeignkeys

join sysobjects fk

on sysforeignkeys.constid = fk.id

join sysobjects ftable

on sysforeignkeys.fkeyid = ftable.id

join sysobjects rtable

on sysforeignkeys.rkeyid = rtable.id

join syscolumns cn

on sysforeignkeys.fkeyid = cn.id and sysforeignkeys.fkey = cn.colid

 1 --外键被参考表

 2 SELECT 主键列ID=b.rkey

 3 ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)

 4 ,外键表ID=b.fkeyid

 5 ,外键表名称=object_name(b.fkeyid)

 6 ,外键名称= c.name

 7 ,外键列ID=b.fkey

 8 ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)

 9 ,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')

10 ,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')

11 FROM sysobjects a

12 join sysforeignkeys b on a.id=b.constid

13 join sysobjects c on a.parent_obj=c.id

14 join syscolumns cn

15 on b.fkeyid = cn.id and b.fkey = cn.colid

16 where a.xtype='f' AND c.xtype='U'

17 and object_name(b.rkeyid)='dt_article'

 1 --外键详细信息

 2 SELECT

 3 f.name AS foreign_key_name

 4 ,OBJECT_NAME(f.parent_object_id) AS table_name

 5 ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name

 6 ,OBJECT_NAME (f.referenced_object_id) AS referenced_object

 7 ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name

 8 ,is_disabled

 9 ,delete_referential_action_desc

10 ,update_referential_action_desc

11 FROM sys.foreign_keys AS f

12 INNER JOIN sys.foreign_key_columns AS fc

13 ON f.object_id = fc.constraint_object_id

14 WHERE f.parent_object_id = OBJECT_ID('test');