获取SQL数据表外键信息列表函数

--创建函数

create function GetFKs(@TableName varchar(50))
returns @re Table(ColumnName nvarchar(50),
                  rTableName nvarchar(50),
                  rColumnName nvarchar(50))
as
begin
    insert into @re
    select t1.name as ColumnName,t2.rtableName as rTableName,t2.name as rColumnName
    from (select col.name, f.constid as temp
          from syscolumns col,sysforeignkeys f
          where f.fkeyid=col.id
                and f.fkey=col.colid
                and f.constid in
                    (select distinct(id)
                     from sysobjects
                     where OBJECT_NAME(parent_obj)=@TableName
                           and xtype='F'
                    )
         ) as t1,
         (select OBJECT_NAME(f.rkeyid) as rtableName,col.name, f.constid as temp
          from syscolumns col,sysforeignkeys f
          where f.rkeyid=col.id
                and f.rkey=col.colid
                and f.constid in
                    (select distinct(id)
                     from sysobjects
                     where OBJECT_NAME(parent_obj)=@TableName
                           and xtype='F'
                    )
         ) as t2
    where t1.temp=t2.temp
    return;
end

 

--使用方法

--select * from GetFKs('表名')

--结果：

--ColumnName, rTableName, rColumnName

--本表外键字段名,外键表名,外键表字段名