DISQUS

DISQUS Hello!  The comments on this profile are unclaimed and thus are unverified.

Do they belong to you? Claim these comments.

Heiner's picture

Unregistered

Feeds

aliases

  • Heiner

Heiner

6 months ago

in Microsoft SQL Server: How to Find the Foreign Keys in a Database on Tech-Recipes
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
Returning? Login