Technical Article

Locate primary key - foreign key relations

,

Recently one of my friend was doing some investigation in a new/unknown database and had really hard time locating the primary key – foreign key relations.

Like it’s really tough to locate these relations when not enforced at database level in terms of primary key and foreign key constraints. But generally all database developers keep the same column names in a database solution and my script is also based on this assumption.

This script will simply search the given column name in all the tables and will also show the relationship (Primary Key / Foreign key) of this column with columns in other tables.

Thanks

Mohit Nayyar

http://mohitnayyar.blogspot.com/

SELECT base.TABLE_NAME, base.COLUMN_NAME, cons.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.COLUMNS base
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE usage ON (base.TABLE_SCHEMA = usage.TABLE_SCHEMA and base.TABLE_NAME = usage.TABLE_NAME and base.COLUMN_NAME = usage.COLUMN_NAME)
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS on (base.TABLE_SCHEMA = usage.TABLE_SCHEMA and usage.CONSTRAINT_NAME = cons.CONSTRAINT_NAME and usage.TABLE_NAME = cons.TABLE_NAME)
WHERE base.COLUMN_NAME = 'StreamDetailID'

Rate

4.14 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

4.14 (7)

You rated this post out of 5. Change rating