Loner Ratio - How many tables do not have relationships in MariaDB database

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:

Learn more about Loner Tables

Query

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;

Columns

  • 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)

Rows

  • Scope of rows: the query returns one row

Sample results

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.

0
There are no comments. Click here to write the first comment.