标签归档:外键信息

mssql SQL Server 2008 查询所有表外键信息的方法分享


摘要:
下文讲述sqlserver 2008获取表外键的方法
实验环境:sql server 2008 R2



实现思路:
主要根据系统表 syscolumns, sysobjects, sysforeignkeys
获取外键列的相关信息,如下:

 
   SELECT 
(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid) 
 as [主键-列名],
object_name(b.rkeyid) as [主键-表名],
object_name(b.fkeyid)  as [外键-表名],
(SELECT name FROM syscolumns 
			WHERE colid=b.fkey AND id=b.fkeyid)  as [外键列名]
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'