--创建函数
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
--本表外键字段名,外键表名,外键表字段名