DISQUS

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

  • Heiner · 10 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