sqlserver 查询表中的主键、外键列及外键表，外表中的主键列

--获取主键信息
EXEC sp_pkeys @table_name='tablename'
 
 
--获取外键 方法一
SELECT Field=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) ,
FKTable=object_name(b.rkeyid),
FKKeyField=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
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.fkeyid)='tablename'
 
 
--获取外键 方法二
select
  oSub.name  AS  [子表名称],
  fk.name AS  [外键名称],
  SubCol.name AS [子表列名],
  oMain.name  AS  [主表名称],
  MainCol.name AS [主表列名]
from
  sys.foreign_keys fk  
JOIN sys.all_objects oSub  
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain 
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols 
ON (fk.object_id = fkCols.constraint_object_id)
    JOIN sys.columns SubCol 
        ON (oSub.object_id = SubCol.object_id  
            AND fkCols.parent_column_id = SubCol.column_id)
    JOIN sys.columns MainCol 
        ON (oMain.object_id = MainCol.object_id  
            AND fkCols.referenced_column_id = MainCol.column_id)
WHERE
  oSub.name  =  'tablename' --AND SubCol.name = '你的列名'