SQL Server: Querying all Foreign key association table information

--From left to right: FOREIGN KEY constraint name, child table name, foreign key column name, parent table name

--use Demodtcms
--FOREIGN key information
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

--FOREIGN key is referenced table
SELECT primary key Column Id=b.rkey
, primary key Column name = (SELECT name from syscolumns WHERE Colid=b.rkey and Id=b.rkeyid)
, FOREIGN key table Id=b.fkeyid
, foreign key table name =object_name (B.fkeyid)
, foreign key name = C.name
, Foreign key column Id=b.fkey
, foreign key Column name = (SELECT name from syscolumns WHERE Colid=b.fkey and Id=b.fkeyid)
Cascade Update =objectproperty (a.id, ' Cnstisupdatecascade ')
, cascade delete =objectproperty (a.id, ' Cnstisdeletecascade ')
From sysobjects a
Join Sysforeignkeys B on A.id=b.constid
Join sysobjects C on a.parent_obj=c.id
Join syscolumns CN
On B.fkeyid = cn.id and B.fkey = Cn.colid
Where a.xtype= ' F ' and c.xtype= ' U '
and object_name (B.rkeyid) = ' dt_article '

--FOREIGN key details source http://technet.microsoft.com/zh-cn/library/ms190196 (v=sql.110). aspx
SELECT
F.name as Foreign_key_name
, object_name (F.PARENT_OBJECT_ID) as table_name
, Col_name (fc.parent_object_id, fc.parent_column_id) as Constraint_column_name
, object_name (f.referenced_object_id) as Referenced_object
, Col_name (fc.referenced_object_id, fc.referenced_column_id) as Referenced_column_name
, is_disabled
, Delete_referential_action_desc
, Update_referential_action_desc
From Sys.foreign_keys as F
INNER JOIN Sys.foreign_key_columns as FC
On f.object_id = fc.constraint_object_id
WHERE f.parent_object_id = object_id (' test ');

SQL Server: Querying all Foreign key association table information