Skip to content

Instantly share code, notes, and snippets.

@ovaistariq

ovaistariq/tables_without_pk.sql

Created October 21, 2014 18:33
Show Gist options
  • Save ovaistariq/56473a1c9eb836b28a7b to your computer and use it in GitHub Desktop.
Save ovaistariq/56473a1c9eb836b28a7b to your computer and use it in GitHub Desktop.
Download ZIP
MySQL - find tables without Primary Key
Raw
tables_without_pk.sql
select tables.table_schema
, tables.table_name
, tables.engine
from information_schema.tables
left join (
select table_schema
, table_name
from information_schema.statistics
group by table_schema
, table_name
, index_name
having
sum(
case
when non_unique = 0
and nullable != 'YES' then 1
else 0
end
) = count(*)
) puks
on tables.table_schema = puks.table_schema
and tables.table_name = puks.table_name
where puks.table_name is null
and tables.table_type = 'BASE TABLE'
and tables.table_schema not in ('performance_schema',
'information_schema', 'mysql');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment