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

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

Query

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
  • no_pk_tables - number of tables without a primary key
  • no_pk_percent - percentage of tables without primary key in all tables

Rows

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.