创建视图以获取所有表的约束

引言

本文介绍如何构建一个视图，该视图检索数据库中所有表格的所有外键约束。

Using the Code

此视图涉及四个表

1. Sysobject：当您设置 xtype='u' 时，从我们的数据库中获取所有表
2. Syscolumns：从我们的数据库中获取所有列名
3. Sysusers：从数据库中获取所有用户
4. Sysforeignkeys：从数据库中获取所有外键

SELECT     SObject3.name AS FK_NAME, SObject3.id AS FK_ID, _
	SUser.name AS TABLE_OWNER, SObject.name AS TABLE_NAME, SObject.id AS TABLE_ID, _
	SColumns.name AS COLUMN_NAME, SColumns.colid, SObject2.name AS REF_TABLE_NAME, _
	SObject2.id AS REF_TABLE_ID, _
         SColumns2.name AS REF_COLUMN_NAME, SColumns2.colid AS REF_TABLE_COLID
FROM         dbo.sysforeignkeys AS SYSFK INNER JOIN
                          (SELECT     uid, id, name
                             FROM         dbo.sysobjects
                             WHERE     (xtype = 'U')) AS _
				SObject ON SYSFK.fkeyid = SObject.id INNER JOIN
                          (SELECT     uid, id, name
                             FROM         dbo.sysobjects AS sysobjects_2
                             WHERE     (xtype = 'U')) AS SObject2 ON _
				SYSFK.rkeyid = SObject2.id INNER JOIN
                          (SELECT     id, colid, name
                             FROM         dbo.syscolumns) AS SColumns ON _
				SYSFK.fkeyid = SColumns.id AND SYSFK.fkey = _
				SColumns.colid INNER JOIN
                          (SELECT     id, colid, name
                             FROM         dbo.syscolumns AS syscolumns_1) _
				AS SColumns2 ON SYSFK.rkeyid = SColumns2.id AND _
				SYSFK.rkey = SColumns2.colid INNER JOIN
                          (SELECT     id, name
                             FROM         dbo.sysobjects AS sysobjects_1) _
			    AS SObject3 ON SYSFK.constid = SObject3.id INNER JOIN
                      dbo.sysusers AS SUser ON SObject.uid = SUser.uid INNER JOIN
                      dbo.sysusers AS SUser2 ON SObject2.uid = SUser2.uid//

摘要

此视图可用于检查任何表格的外键。 如果您需要查看外键，可以设置 where 条件表名并显示所有键。

历史

• 2007年9月9日：初始发布