DISQUS

DISQUS Hello! Tech-Recipes is using DISQUS, a powerful comment system, to manage its comments. Learn more.

Community Page

Tech-Recipes

Cookbook of Tech Tutorials
Jump to original thread »
Author

Microsoft SQL Server: How to Find the Foreign Keys in a Database

Started by qdideas · 9 months ago

A foreign key is a column or columns that are used to enforce a link between data in two tables. While SQL Server gives you no quick and easy way to view the foreign keys in a database, this quick query will give you that information. It comes in handy when trying to troubleshoot Foreign Key Cons ... Continue reading »

1 comment

  • Heiner

    6 months ago

    Hi!

    Your sql seems to have problems with foreign keys consisting of several columns. I get for example:

    IE_FK_ADDRESS_USER IE_ADDRESS INST_ID IE_USER HOLDER_ID
    IE_FK_ADDRESS_USER IE_ADDRESS INST_ID IE_USER INST_ID
    IE_FK_ADDRESS_USER IE_ADDRESS USER_HOLDER_ID IE_USER HOLDER_ID
    IE_FK_ADDRESS_USER IE_ADDRESS USER_HOLDER_ID IE_USER INST_ID

    but just the second and third row are okay. I am far from being an sql expert, but the following select returns the correct result for my database:

    SELECT
    ConstraintName = a.CONSTRAINT_NAME,
    FromTable = c.TABLE_NAME,
    FromColumn = c.COLUMN_NAME,
    ToTable = d.TABLE_NAME,
    ToColumn = d.COLUMN_NAME
    FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS a,
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE d
    WHERE a.CONSTRAINT_TYPE = 'FOREIGN KEY'
    and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
    and a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
    and b.UNIQUE_CONSTRAINT_NAME = d.CONSTRAINT_NAME
    and c.ORDINAL_POSITION = d.ORDINAL_POSITION
    ORDER BY a.CONSTRAINT_NAME, c.ORDINAL_POSITION

    Best regards,


    Heiner
    reply

Add New Comment

Returning? Login