Find All Tables With No Primary Key

Posted in: Technical Track

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;

Interested in working with Sheeri? Schedule a tech call.

9 Comments. Leave new

Hi! Good ones. Can you forge `em?

Reply

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.

Reply

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

Reply

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

Reply

Samit,

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

Reply

Hi,

It’s really helpful for me.Thanks a lot!!!!

Reply

Way too complicated. This is all you need:

SELECT name AS “Table” FROM sys.TABLES
WHERE OBJECTPROPERTY(object_id,’TableHasPrimaryKey’) = 0;

To do the same for all databases:

exec SP_MSForEachDB ‘use [?]; select DB_NAME(), name as “Table” FROM sys.TABLES
WHERE OBJECTPROPERTY(object_id,”TableHasPrimaryKey”) = 0;’

Reply

^^^ Ooops, that is for SQL Server. Sorry I didn’t catch that this was a MySQL thread!

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *