useful SQL — useful listing to find foreign key refernces

useful listing to find foreign key refernces

——LISTING 2: Code That Locates Possible Foreign Key Fields

SELECT c1.column_name AS COLUMN_NAME,
c1.table_name AS PK_TABLE_NAME,
c2.table_name AS FK_TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1 INNER JOIN
INFORMATION_SCHEMA.COLUMNS c2
ON c1.COLUMN_NAME = c2.COLUMN_NAME
AND c1.DATA_TYPE = c2.DATA_TYPE
AND c1.TABLE_NAME c2.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON c1.COLUMN_NAME = ccu.COLUMN_NAME
AND c1.TABLE_NAME = ccu.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLES T
ON t.TABLE_NAME = c2.TABLE_NAME
WHERE tc.CONSTRAINT_TYPE IN (‘PRIMARY KEY’, ‘UNIQUE’) — Anything a foreign
— key can reference
AND t.TABLE_TYPE = ‘BASE TABLE’ — Eliminate views.
AND t.TABLE_NAME NOT LIKE ‘sys%’ — Eliminate system tables.
AND t.TABLE_NAME LIKE ‘Address’

Advertisements

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