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;

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;

6 Responses to “Find All Tables With No Primary Key”

1. Jay Pipes Says:
   January 28th, 2008 at 3:18 pm

   Hi! Good ones. Can you forge `em?

2. Roland Bouman Says:
   January 28th, 2008 at 6:59 pm

   Hi Sheeri!

   Heads up, you do CONCAT(t.table_name,”.”,t.table_schema) and you have a comma too much after

   as tbl,

   that said, I think it’s somewhat cleaner and perhaps faster if you use

   TABLE_CONSTRAINTS rather than KEY_COLUMN_USAGE

   and

   CONSTRAINT_TYPE = ‘PRIMARY KEY’ rather than CONSTRAINT_NAME = ‘PRIMARY’.

   Also, if you don’t check for the TABLE_TYPE, you will report VIEWs as not having a primary key. I’m not sure if that’s intended but if not I recommend including a

   TABLE_TYPE = ‘BASE TABLE’

   in the where and this will automatically get rid of the tables in the information_schema.

   SELECT t.table_schema
   , t.table_name
   FROM information_schema.TABLES t
   LEFT JOIN information_schema.TABLE_CONSTRAINTS c
   ON t.table_schema = c.table_schema
   AND t.table_name = c.table_name
   AND ‘PRIMARY KEY’ = c.constraint_type
   WHERE c.constraint_name IS NULL
   AND t.table_type = ‘BASE TABLE’;

   Also, I’m not sure if it’s intended, but the second query will return multiple rows if the primary key is a composite key (one row for each column in the pk). For reporting I would probably use GROUP_CONCAT the columns to get exactly as many rows as there are primary keys.

3. Stoner Says:
   January 28th, 2008 at 7:20 pm

   I’ll incorporate variations of these into my MyHelper project (sourceforge.net).

4. Sheeri Cabral Says:
   February 6th, 2008 at 10:21 pm

   On the forge at:

   http://forge.mysql.com/snippets/view.php?id=119

5. Samit Katiyar Says:
   September 24th, 2008 at 3:45 am

   I tried to execute the mentioned query to get All tables and their primary keys, if exist, BUT IT”S GIVING ERROR:
   “Invalid Number of Argument” at concat…………

   Can u plz explain how to get all tables having a common PK or FK……in PL/SQL Dev

   Thanks

6. Sheeri Cabral Says:
   September 24th, 2008 at 9:00 am

   Samit,

   MySQL does not use PL/SQL so I’m afraid I cannot help you.

Leave a Reply