Query below lists all tables that do not have foreign keys.
select tab.table_schema, tab.table_name, '>- No FKs' as foreign_keys from information_schema.tables tab left join (select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY') fks on tab.table_name = fks.table_name and tab.table_schema = fks.table_schema where fks.constraint_type is null and tab.table_type = 'BASE TABLE' order by tab.table_schema, tab.table_name;
- table_schema - name of the schema in which table resides
- table_name - table name
- foreign_keys - symbol indicating lack of FKs
- One row represents one without foreign keys
- Scope of rows: all tables without foreign keys (do not refer to other tables)
- Ordered by schema name and table name