主外键表关联关系

 

 

 SELECT 
    OBJECT_NAME(constid) 外键,
    OBJECT_NAME(fkeyid) 外键表, 
    (SELECT name FROM syscolumns c1 WHERE  c1.colid=a.fkey AND c1.id=a.fkeyid) 外键表字段,
    OBJECT_NAME(rkeyid) AS 主键表,
    (SELECT name FROM syscolumns  AS c WHERE c.colid=a.rkey AND c.id=a.rkeyid) 主键表字段,
    a.rkey 外键字段排序号,a.rkeyid 外键表Id
 FROM sysforeignkeys a
 LEFT JOIN sysobjects b ON b.id=a.constid
 --LEFT JOIN syscolumns c on c.colid=a.rkey AND c.id=a.rkeyid
 --LEFT JOIN syscolumns c1 on c1.colid=a.fkey AND c1.id=a.fkeyid
  
  
 SELECT 
 b.rkeyid,object_name(b.rkeyid),
 主键列ID=b.rkey   
  ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)   
    ,外键表ID=b.fkeyid   
    ,外键表名称=object_name(b.fkeyid)   
    ,外键列ID=b.fkey   
    ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)   
    ,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')   
    ,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')   
FROM sysobjects a   
    join sysforeignkeys b on a.id=b.constid   
    join sysobjects c on a.parent_obj=c.id   
where a.xtype='f' AND c.xtype='U'   
    and object_name(b.rkeyid)='Sys_Users'