VFK. A View for Table Relationships; FOREIGN - SQL, database, informix, oracle, SQL Server

VFK. A View for Table Relationships; FOREIGN KEY and REFERENCED Table/Column names and its relations

	Print
	Email

Submitted on: 4/8/2003 1:37:45 AM
By: Raghavendra Narayana
Level: Intermediate
User Rating:

By 13 Users
Compatibility:SQL Server 2000

Users have accessed this article 4394 times.

(About the author)

Screwed up with the Table relationships??? Here is a Database View to see the relationships of tables. It is easy to identify the foreign/referenced table/column names and its relationships using this view.

Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.


Create this in your database and grant permission to public.
--********************* 
CREATE VIEW VFK AS 
--Author: N.Raghavendra 
--Created on: 7-Apr-2003 
--Version: 1.0 
SELECT 
--Name of the FOREIGN KEY constraint 
SO3.NAME FK_NAME, 
--Unique ID of FOREIGN KEY constraint 
SO3.ID FK_ID, 
-- Owner of the table with the FOREIGN KEY constraint 
SU.NAME TABLE_OWNER, 
--Name of the table with the FOREIGN KEY constraint 
SO.NAME TABLE_NAME, 
--Object identification number. 
SO.ID TABLE_ID, 
--Name of the Column with the FOREIGN KEY constraint 
SC.NAME COLUMN_NAME, 
--Position of the column in the FOREIGN KEY constraint 
SC.COLID COLID, 
--Owner of the table referenced in the FOREIGN KEY constraint 
SU2.NAME REF_TABLE_OWNER, 
--Name of the table referenced in the FOREIGN KEY constraint 
SO2.NAME REF_TABLE_NAME, 
--Object identification number of the table referenced in the FOREIGN KEY constraint. 
SO2.ID REF_TABLE_ID, 
--Name of the column referenced in the FOREIGN KEY constraint 
SC2.NAME REF_COLUMN_NAME, 
--Position of the column in the reference column list 
SC2.COLID REF_TABLE_COLID 
FROM SYSFOREIGNKEYS SYSFK 
--Foreign Key Constraint - Table info 
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = 'U') SO 
ON SYSFK.FKEYID = SO.ID 
--Referenced in the FOREIGN KEY constraint - Table info 
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = 'U') SO2 
ON SYSFK.RKEYID = SO2.ID 
--Foreign Key Constraint - Column info 
INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS) SC ON SYSFK.FKEYID = 
SC.ID AND SYSFK.FKEY = SC.COLID 
--Referenced in the FOREIGN KEY constraint - Column info 
INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS) SC2 ON SYSFK.RKEYID 
= SC2.ID AND SYSFK.RKEY = SC2.COLID 
--Name and ID of the FOREIGN KEY constraint 
INNER JOIN (SELECT ID, NAME FROM SYSOBJECTS) SO3 ON SYSFK.CONSTID = 
SO3.ID 
--Foreign Key Constraint - Owner info 
INNER JOIN SYSUSERS SU ON SO.UID = SU.UID 
--Referenced in the FOREIGN KEY constraint - Owner info 
INNER JOIN SYSUSERS SU2 ON SO2.UID = SU2.UID 
GO 
GRANT SELECT ON VFK TO PUBLIC 
GO 
--**************** 
Planet Source Boys and Gals, encourage 
Brand New Young Talents
like me by rating/commenting/giving feedback/giving suggestions.

Other 9 submission(s) by this author

Report Bad Submission

Your Vote!

Other User Comments

4/9/2003 8:04:53 AM:
I'll try it out. thanks.

Add Your Feedback!

Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.