Metadata Queries: Listing Foreign Keys Without Corresponding Indexes
DB2
Query SYSCAT.TABCONST, SYSCAT.KEYCOLUSE, SYSCAT.INDEXES, and SYSCAT.INDEXCOLUSE:
1 select fkeys.tabname,
2 fkeys.constname,
3 fkeys.colname,
4 ind_cols.indname
5 from (
6 select a.tabschema, a.tabname, a.constname, b.colname
7 from syscat.tabconst a,
8 syscat.keycoluse b
9 where a.tabname = 'EMP'
10 and a.tabschema = 'SMEAGOL'
11 and a.type = 'F'
12 and a.tabname = b.tabname
13 and a.tabschema = b.tabschema
14 ) fkeys
15 left join
16 (
17 select a.tabschema,
18 a.tabname,
19 a.indname,
20 b.colname
21 from syscat.indexes a,
22 syscat.indexcoluse b
23 where a.indschema = b.indschema
24 and a.indname = b.indname
25 ) ind_cols
26 on ( fkeys.tabschema = ind_cols.tabschema
27 and fkeys.tabname = ind_cols.tabname
28 and fkeys.colname = ind_cols.colname )
29 where ind_cols.indname is null
Oracle
Query SYS.ALL_CONS_COLUMNS, SYS.ALL_CONSTRAINTS, and SYS.ALL_ IND_COLUMNS:
1 select a.table_name,
2 a.constraint_name,
3 a.column_name,
4 c.index_name
5 from all_cons_columns a,
6 all_constraints b,
7 all_ind_columns c
8 where a.table_name = 'EMP'
9 and a.owner = 'SMEAGOL'
10 and b.constraint_type = 'R'
11 and a.owner = b.owner
12 and a.table_name = b.table_name
13 and a.constraint_name = b.constraint_name
14 and a.owner = c.table_owner (+)
15 and a.table_name = c.table_name (+)
16 and a.column_name = c.column_name (+)
17 and c.index_name is null
PostgreSQL
Query INFORMATION_SCHEMA.KEY_COLUMN_USAGE, INFORMATION_ SCHEMA.REFERENTIAL_CONSTRAINTS, INFORMATION_SCHEMA.COL-UMNS, and PG_CATALOG.PG_INDEXES:
1 select fkeys.table_name,
2 fkeys.constraint_name,
3 fkeys.column_name,
4 ind_cols.indexname
5 from (
6 select a.constraint_schema,
7 a.table_name,
8 a.constraint_name,
9 a.column_name
10 from information_schema.key_column_usage a,
11 information_schema.referential_constraints b
12 where a.constraint_name = b.constraint_name
13 and a.constraint_schema = b.constraint_schema
14 and a.constraint_schema = 'SMEAGOL'
15 and a.table_name = 'EMP'
16 ) fkeys
17 left join
18 (
19 select a.schemaname, a.tablename, a.indexname, b.column_name
20 from pg_catalog.pg_indexes a,
21 information_schema.columns b
22 where a.tablename = b.table_name
23 and a.schemaname = b.table_schema
24 ) ind_cols
25 on ( fkeys.constraint_schema = ind_cols.schemaname
26 and fkeys.table_name = ind_cols.tablename
27 and fkeys.column_name = ind_cols.column_name )
28 where ind_cols.indexname is null
MySQL
You can use the SHOW INDEX command to retrieve index information such as index name, columns in the index, and ordinal position of the columns in the index. Additionally, you can query INFORMATION_SCHEMA.KEY_COLUMN_USAGE to list the foreign keys for a given table. In MySQL 5, foreign keys are said to be indexed automatically, but can in fact be dropped. To determine whether a foreign key column's index has been dropped you can execute SHOW INDEX for a particular table and compare the output with that of INFORMATION_SCHEMA.KEY_ COLUMN_USAGE.COLUMN_NAME for the same table. If the COLUMN_NAME is listed in KEY_COLUMN_USAGE but is not returned by SHOW INDEX, you know that column is not indexed.
SQL Server
Query SYS.TABLES, SYS.FOREIGN_KEYS, SYS.COLUMNS, SYS.INDEXES, and SYS.INDEX_COLUMNS:
1 select fkeys.table_name,
2 fkeys.constraint_name,
3 fkeys.column_name,
4 ind_cols.index_name
5 from (
6 select a.object_id,
7 d.column_id,
8 a.name table_name,
9 b.name constraint_name,
10 d.name column_name
11 from sys.tables a
12 join
13 sys.foreign_keys b
14 on ( a.name = 'EMP'
15 and a.object_id = b.parent_object_id
16 )
17 join
18 sys.foreign_key_columns c
19 on ( b.object_id = c.constraint_object_id )
20 join
21 sys.columns d
22 on ( c.constraint_column_id = d.column_id
23 and a.object_id = d.object_id
24 )
25 ) fkeys
26 left join
27 (
28 select a.name index_name,
29 b.object_id,
30 b.column_id
31 from sys.indexes a,
32 sys.index_columns b
33 where a.index_id = b.index_id
34 ) ind_cols
35 on ( fkeys.object_id = ind_cols.object_id
36 and fkeys.column_id = ind_cols.column_id )
37 where ind_cols.index_name is null
