Create View To Get All Constraints For Any Tables

Introduction

This article is about building a view that retrieves all foreign keys constraints for all tables in database.

Using the Code

This view relation between four tables:

1. Sysobject: Get All tables from our database when you set xtype='u'
2. Syscolumns: Get All columns Name from our database
3. Sysusers: Get all users from database
4. Sysforeignkeys: Get all foreign keys from database

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//

Summary

This view can be used to check on foreign keys for any tables. If you need to see foreign key, you can set where condition table name and show all keys.

History

• 9^th September, 2007: Initial post