Find All Tables With No Primary Key
Posted by Sheeri Cabral on Jan 28, 2008
A friend asked for this, so I thought it’d be helpful:
All tables with no primary key:
use INFORMATION_SCHEMA; select CONCAT(t.table_name,".",t.table_schema) as tbl, from TABLES AS t LEFT JOIN KEY_COLUMN_USAGE AS c ON (t.TABLE_NAME=c.TABLE_NAME AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA AND constraint_name='PRIMARY') WHERE t.table_schema!="information_schema" AND constraint_name IS NULL;
All tables and their primary keys, if exist:
use INFORMATION_SCHEMA; select CONCAT(t.table_name,".",t.table_schema) as tbl, c.column_name,c.constraint_name from TABLES AS t LEFT JOIN KEY_COLUMN_USAGE AS c ON (t.TABLE_NAME=c.TABLE_NAME AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA AND constraint_name='PRIMARY') WHERE t.table_schema!="information_schema" order by constraint_name;
