Skip to main content

Posts

Showing posts from February, 2013

Query to find Related tables, related by Foreign Key and Primary Key

Many times, When we come to a situation to start our work from already developed project, Where Foreign Keys and Primary Keys are already developed in Database, It is Necessary for us to know which table is referring to which table as with Primary Key and Foreign Key Constraint. For this Purpose, We have System Tables which will Help us to find the Primary key and Foreign key and related Tables. The query is as follows: USE ADVENTUREWORKS2008 GO SELECT OBJECT_NAME ( SFK . PARENT_OBJECT_ID ) AS [FORIEGN KEY TABLE] , COL_NAME ( SFKC . PARENT_OBJECT_ID , SFKC . PARENT_COLUMN_ID ) AS [FORIEGN KEY COLUMN] , OBJECT_NAME ( SFK . REFERENCED_OBJECT_ID ) AS [PRIMARY KEY TABLE] , COL_NAME ( SFKC . REFERENCED_OBJECT_ID , SFKC . REFERENCED_COLUMN_ID ) AS [PRIMARY KEY COLUMN] FROM SYS . FOREIGN_KEYS AS SFK INNER JOIN SYS . FOREIGN_KEY_COLUMNS AS SFKC ON SFK . OBJECT_ID = SFKC . CONSTRAINT_OBJECT_ID Here I'm trying to find Related tables under Primary key an