Handy Query : List of MySQL Tables Without Primary Key

Mikael HOUNDEGNON

5 years ago

Here is my favorite handy query to get the list of tables in MySQL without Primary Key :

USE INFORMATION_SCHEMA;
SELECT TABLES.table_schema, TABLES.table_name
FROM 	TABLES LEFT JOIN KEY_COLUMN_USAGE AS c  ON ( TABLES.TABLE_NAME = c.TABLE_NAME AND c.CONSTRAINT_SCHEMA = TABLES.TABLE_SCHEMA AND c.constraint_name = 'PRIMARY')
WHERE 	TABLES.table_schema not in ('information_schema','performance_schema','mysql')
AND 	TABLES.table_type = 'BASE TABLE'
AND 	c.constraint_name IS NULL;

USE INFORMATION_SCHEMA;

SELECT TABLES.table_schema, TABLES.table_name

FROM TABLES LEFT JOIN KEY_COLUMN_USAGE AS c ON ( TABLES.TABLE_NAME = c.TABLE_NAME AND c.CONSTRAINT_SCHEMA = TABLES.TABLE_SCHEMA AND c.constraint_name = 'PRIMARY')

WHERE TABLES.table_schema not in ('information_schema','performance_schema','mysql')

AND TABLES.table_type = 'BASE TABLE'

AND c.constraint_name IS NULL;

This my shortest article ever, hope it will help someone, enjoy !!