SQL SERVER – 2008 – Find Relationship of Foreign Key and Primary Key using T-SQL – Find Tables With Foreign Key Constraint in Database

While searching for how to find Primary Key and Foreign Key relationship using T-SQL, I came across my own blog article written earlier SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database. It is really handy script and not found written on line anywhere. This is one really unique script and must be bookmarked. There may be situations when there is need to find out on relationship between Primary Key and Foreign Key.

I have modified my previous script to add schema name along with table name. It would be really great if any of you can improve on this script.

USE AdventureWorks;
GO
SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
GO

 

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

23 thoughts on “SQL SERVER – 2008 – Find Relationship of Foreign Key and Primary Key using T-SQL – Find Tables With Foreign Key Constraint in Database

  1. Always fun to turn a script for 1 DB to ALL DB’s

    sp_MSForEachDB ‘
    if ”?” NOT IN (”tempdb”,”master”,”model”,”msdb”) USE ?
    SELECT
    DBName = DB_NAME()
    ,f.name AS ForeignKey
    ,SCHEMA_NAME(f.SCHEMA_ID) SchemaName
    ,OBJECT_NAME(f.parent_object_id) AS TableName
    ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName
    ,SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName
    ,OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName
    ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
    INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id ON f.OBJECT_ID = fc.constraint_object_id

  2. Easily, the article is in reality the sweetest on this laudable topic. I agree with your conclusions and will eagerly look forward to your approaching updates. Just saying thanks will not just be enough, for the tremendous lucidity in your writing. I will immediately grab your rss feed to stay privy of any updates. Good work and much success in your business efforts!

  3. Pinal – You got me. (From your script)How is follow script able to work? An inner join without any clause and another with two conditions not seperated by any operator. Please explain.


    INNER JOIN
    sys.foreign_key_columns AS fc
    INNER JOIN
    sys.objects AS o
    ON o.OBJECT_ID = fc.referenced_object_id
    ON f.OBJECT_ID = fc.constraint_object_id

  4. Hello Mac,

    If I remove the alias then the query would like following:

    FROM sys.foreign_keys
    INNER JOIN sys.foreign_key_columns
    INNER JOIN sys.objects
    ON sys.objects.OBJECT_ID = sys.foreign_key_columns.referenced_object_id
    ON sys.foreign_keys.OBJECT_ID = sys.foreign_key_columns.constraint_object_id

    Notice that both ON clause are on different tables.

    Regards,
    Pinal Dave

  5. Hi..

    i am facing a problem while retreiving foreign keys.
    i am passing foreign key table in below your query but i returns some extra columns or you can say some of its column’s data is beign duplicate.
    how can i handle this?
    thnxk

    SELECT f.name AS ForeignKey,
    SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
    SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
    INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
    ON f.OBJECT_ID = fc.constraint_object_id

  6. Sorry i sent a wrong query . i am trying this one:

    select ISNULL(c.constraint_name,”) ConstraintName,

    ISNULL(cfk.table_name ,”) FK_Table_name,

    ISNULL(kcu.column_name ,”) FK_Column_name,

    ISNULL(cpk.table_name ,”) PK_Table_name,

    ISNULL(pkt.column_name ,”) PK_COlumn_name

    from information_schema.REFERENTIAL_CONSTRAINTS c

    INNER JOIN information_schema.TABLE_CONSTRAINTS cpk on c.unique_constraint_name = cpk.constraint_name

    INNER JOIN information_schema.TABLE_CONSTRAINTS cfk on c.constraint_name = cfk.constraint_name

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.constraint_name = kcu.constraint_name

    INNER JOIN
    (
    SELECT tci.table_name, kcui.column_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tci
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcui on tci.constraint_name = kcui.constraint_name
    WHERE tci.CONSTRAINT_TYPE = ‘PRIMARY KEY’

    ) PKT ON PKT.TABLE_NAME = CPK.TABLE_NAME
    GROUP BY c.constraint_name,cfk.table_name, kcu.column_name, cpk.table_name, pkt.column_name
    HAVING count(pkt.column_name) = 1 and
    cfk.table_name = ‘bp_bank’
    ORDER BY 1,2,3,4

  7. This was great. I was wondering though..can you provide an example where if a relationship exists…

    Drop the relationship/contstraint

    What I want to do is create table and its relationships, but if I need to drop the parent table, I want to drop the respective relationships before hand.

    Thanks.

  8. Dave,

    Your knowledge and examples are great. I get just the answers I’m looking for.

    Thank you, thank you, thank you!

  9. Pinal, I often come across your blog when trying to solve a SQL issue. Thank you so much for all of the work you do to provide such a great resource! It is much appreciated

  10. Hi Pinal,

    I am a beginner in SQLServer.i want to update and delete more then two tables
    using merge concepts.so kindly let me send query asap….
    Note:Tables are relate with foreign key,primary key constrains..

    Thanks,

  11. Hi Pinal,
    This is my sample tables…

    Table1
    id name country
    1 xyz US
    2 xyz India
    ————————–
    Table2

    id aaa bbb ccc
    1
    2
    —————————
    Table 3

    id xx yy zz
    1
    2

  12. i want tables like this…

    Table1
    id name country

    2 xyz India
    ————————–
    Table2

    id aaa bbb ccc
    2
    2
    —————————
    Table 3

    id xx yy zz
    2
    2

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #018 | SQL Server Journey with SQL Authority

  14. If I wanted to find all the possible joins (ie. Inner, left or right) as it pertains one table to another table, what SQL query in 2005 would be the best to use? I am trying to print out a data schema for relationship between all tables. It would make it easier for querying the database? Need help. Any help I would greatly appreciate. Thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s