How many tables don't have primary keys (with percentage) in MariaDB database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-01-17

Table of Contents:


    The query below shows how many tables without primary keys there are in the user databases (schemas), and what percentage of the total tables represents.

    Query

    Generic query for the specified database (schema)

    select count(*) as all_tables,
           count(*) - count(tco.constraint_type) as no_pk_tables,
           cast( 100.0*(count(*) - count(tco.constraint_type)) / count(*)
                as decimal(5,2)) as no_pk_percent
    from information_schema.tables tab
    left join information_schema.table_constraints tco
              on tab.table_schema = tco.table_schema
              and tab.table_name = tco.table_name
              and tco.constraint_type = 'PRIMARY KEY'
    where tab.table_type = 'BASE TABLE'
        -- and tab.table_schema = 'database_name' -- put your database name here
          and tab.table_schema not in('mysql', 'information_schema',
                                      'sys','performance_schema');
    

    Columns

    • all_tables - number of all tables in the database (schema)
    • no_pk_tables - number of tables without a primary key
    • no_pk_percent - percentage of tables without primary key

    Rows

    The query returns just one row.

    Sample results

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept