This query listed tables that do not have foreign keys, which means that they are not referencing any table or that they are not on the "many" side of FK.
The query below lists something a little different - tables that are not referencing and are not referenced by other tables. Something that can be called as Loner Tables. This diagram illustrates the concept:
select all_tables as table_count, no_rel as loner_tables, concat(cast(100.0*(no_rel/all_tables) as decimal(5,2)), '%') as loner_ratio from (select count(distinct concat(tab.table_schema, '.', tab.table_name)) as all_tables, SUM(case when ref.constraint_name is null and ref_by.constraint_name is null then 1 else 0 end) as no_rel from information_schema.tables tab left join information_schema.referential_constraints ref on tab.table_schema = ref.constraint_schema and tab.table_name = ref.table_name left join information_schema.referential_constraints ref_by on tab.table_schema = ref_by.unique_constraint_schema and tab.table_name = ref_by.referenced_table_name where tab.table_type = 'BASE TABLE' and tab.table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') ) temp;
- table_count - number of tables in the database (schema)
- loner_tables - number of Loner Tables in the database (schema)
- loner_ratio - Loner Ratio - % of Loner Tables in the database (schema)
- Scope of rows: the query returns one row
The Loner Ratio in the Airlines database (schema) is 12.5%, which means that 12.5% of the tables are not related by foreign keys to any other table.