Handy Query : List of MySQL Tables Without Primary Key

Mikael HOUNDEGNON April 11, 2019 MySQL-MariaDB

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 !!

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

Handy Query : List of MySQL Tables Without Primary Key

About Mikael HOUNDEGNON

Leave a Reply Cancel reply