sql server查询一个表的外键引用情况（联合外键）

先创建一个主表：

CREATE TABLE
    dbo.TestMain
    (
        col1 bigint,
        col2 bigint,
        col3 bigint,
        PRIMARY KEY (col1, col2)      --联合主键
    )

再创建一个从表：

CREATE TABLE
    AccountsData.dbo.TestSlave
    (
        s1 bigint       ,
        s2 bigint       ,
        s3 bigint       ,
        s4 bigint       ,
        PRIMARY KEY (s1),            --自身的主键
        CONSTRAINT TestSlave_fk1 FOREIGN KEY (s2, s3) REFERENCES AccountsData.dbo.TestMain (col1, col2)  -- 联合外键（呵呵，这种情况不多，但是也有的）
    )    

造点数据，其实这里也用不到，但是还是来电数据比较好，有助于了解外键引用关系

insert TestMain (col1,col2,col3)
select 1,1,0 union
select 1,2,0 union
select 1,3,0 union
select 2,2,0


insert TestSlave (s1,s2,s3,s4)
select 1,1,1,0 union
select 2,1,2,0 union
select 3,1,3,0 union
select 4,2,2,0

select * from TestMain
select * from TestSlave

查询一个表的外键引用情况（联合外键）的sql 如下：

SELECT
    fk.name     fkname    ,
    ftable.name ftablename,
    fcn.name    fkcol     ,
    rtable.name ftablename,
    rcn.name    rkcol
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 fcn ON sysforeignkeys.fkeyid = fcn.id AND sysforeignkeys.fkey = fcn.colid
JOIN
    syscolumns rcn ON sysforeignkeys.rkeyid = rcn.id AND sysforeignkeys.rkey = rcn.colid
WHERE
    ftable.id = Object_ID('TestSlave')             --table name

对于“联合外键”来说，外键都是有外键名称的，统一外键名称，会有多条记录（有几个columns组成联合外键，就有几条记录！）：

fkname         ftablename  fkcol  ftablename  rkcol  
-------------  ----------  -----  ----------  -----  
TestSlave_fk1  TestSlave   s2     TestMain    col1   
TestSlave_fk1  TestSlave   s3     TestMain    col2