Click here to Skip to main content
12,348,674 members (55,768 online)
Click here to Skip to main content
Add your own
alternative version

Stats

18.3K views
17 bookmarked
Posted

Create View To Get All Constraints For Any Tables

, 9 Sep 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Create View To Get Constraints

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

  • 9th September, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rami Said Abd Alhalim
Team Leader Al-ghanem Trading & contracting co ltd
Jordan Jordan
No Biography provided

You may also be interested in...

Comments and Discussions

 
AnswerList All The Constraint of Database Pin
robinthomas_inr12-Aug-08 8:27
memberrobinthomas_inr12-Aug-08 8:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160621.1 | Last Updated 9 Sep 2007
Article Copyright 2007 by Rami Said Abd Alhalim
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid