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.