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:
Sysobject
: Get All tables from our database when you set xtype='u'
Syscolumns
: Get All columns Name from our database Sysusers
: Get all users from database 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
- 9th September, 2007: Initial post