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

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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.