Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Databases » Revisions
 

Find All Relationships of a Primary Key

, 9 Jul 2012
Rate this:
Please Sign up or sign in to vote.
Find all relationships of a Primary Key.
This is an old version of the currently published tip/trick.

Introduction 

Primary Key

As we know Primary Key is one column or combination of columns which contains values that uniquely identify each row in table.

Foreign Key

As we know Foreign Key is a column or combination of two or more column for defining relationship between two or more tables or we can say establish a link between data of two or more tables.

fk_pk

But my question is, while using a huge database which has 1000’s of tables, how can we find that which table is referenced to how many tables. How can we find all FK tables list? For this purpose I made a SQL query which will return the TABLE name in which the PK is associated as FK.

Solution

DECLARE @SEARCHTABLENAME VARCHAR(MAX)
SET @SEARCHTABLENAME = 'PRIMARYKEYTABLE' --Set table name(Primary Key table name) 
    for searching it's references(Foreign Key table list)
SELECT
    PKTableInfo.TABLE_NAME AS PK_Table_Name
    , ConstarintReference.UNIQUE_CONSTRAINT_NAME AS PK_CONSTRAINT_NAME
    , STUFF( (
                SELECT
                    ',' + kcu.COLUMN_NAME
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  AS kcu
                WHERE kcu.TABLE_CATALOG = PKTableInfo.TABLE_CATALOG
                    AND kcu.TABLE_SCHEMA = PKTableInfo.TABLE_SCHEMA
                    AND kcu.TABLE_NAME = PKTableInfo.TABLE_NAME
                    AND kcu.CONSTRAINT_CATALOG = PKTableInfo.CONSTRAINT_CATALOG
                    AND kcu.CONSTRAINT_SCHEMA = PKTableInfo.CONSTRAINT_SCHEMA
                    AND kcu.CONSTRAINT_NAME = PKTableInfo.CONSTRAINT_NAME
                ORDER BY kcu.ORDINAL_POSITION
                FOR XML PATH('')
            ), 1, 1, '') AS [PK_CONSTRAINT_COLUMNS]
    , FKTableInfo.TABLE_NAME AS FK_Table_Name
    , ConstarintReference.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
    , STUFF( (
                SELECT
                    ',' + kcu.COLUMN_NAME
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  AS kcu
                WHERE kcu.TABLE_CATALOG = FKTableInfo.TABLE_CATALOG
                    AND kcu.TABLE_SCHEMA = FKTableInfo.TABLE_SCHEMA
                    AND kcu.TABLE_NAME = FKTableInfo.TABLE_NAME
                    AND kcu.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG
                    AND kcu.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA
                    AND kcu.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME
                ORDER BY kcu.ORDINAL_POSITION
                FOR XML PATH('')
            ), 1, 1, '') AS [FK_CONSTRAINT_COLUMNS]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS  AS ConstarintReference
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS PKTableInfo 
        ON PKTableInfo.CONSTRAINT_CATALOG = ConstarintReference.UNIQUE_CONSTRAINT_CATALOG
        AND PKTableInfo.CONSTRAINT_SCHEMA = ConstarintReference.UNIQUE_CONSTRAINT_SCHEMA
        AND PKTableInfo.CONSTRAINT_NAME = ConstarintReference.UNIQUE_CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS FKTableInfo ON 
        ConstarintReference.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG
        AND ConstarintReference.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA
        AND ConstarintReference.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME
WHERE PKTableInfo.TABLE_NAME = @SEARCHTABLENAME
ORDER BY PK_Table_Name, FK_Table_Name
GO

Result

result

License

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

About the Author

Tejas Vaishnav
Software Developer Priya Softwebsolutions
India India
No Biography provided

Comments and Discussions


Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
 
GeneralMy vote of 5 PinmemberStrange_Pirate30-Sep-12 23:15 
GeneralMy vote of 4 PinmemberNisarg S Shah18-Jul-12 1:41 
GeneralRe: My vote of 4 PinmemberTejas_Vaishnav18-Jul-12 9:28 
GeneralMy vote of 1 PinmemberFarhan Ghumra11-Jul-12 3:13 
GeneralSo this is also yet another theft from someone.. [modified] Pinmemberkicker201210-Jul-12 3:06 
GeneralRe: So this is also yet another theft from someone.. PinmemberTejas_Vaishnav11-Jul-12 2:58 
GeneralRe: So this is also yet another theft from someone.. PinmemberMarcus Kramer12-Jul-12 4:15 
GeneralRe: So this is also yet another theft from someone.. PinmemberTejas_Vaishnav11-Jul-12 3:00 
GeneralRe: So this is also yet another theft from someone.. PinmemberStrange_Pirate29-Sep-12 0:20 
GeneralMessage Removed PinmemberTejas Vaishnav29-Sep-12 0:29 
GeneralRe: So this is also yet another theft from someone.. PinmemberStrange_Pirate29-Sep-12 0:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web03 | 2.8.140721.1 | Last Updated 9 Jul 2012
Article Copyright 2012 by Tejas Vaishnav
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid