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;
Category: Group Blog Posts, MySQL
Tags: information_schema, MySQL, primary key
Hi! Good ones. Can you forge `em?
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.
I’ll incorporate variations of these into my MyHelper project (sourceforge.net).
On the forge at:
http://forge.mysql.com/snippets/view.php?id=119
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
Samit,
MySQL does not use PL/SQL so I’m afraid I cannot help you.
Hi,
It’s really helpful for me.Thanks a lot!!!!
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;’
^^^ Ooops, that is for SQL Server. Sorry I didn’t catch that this was a MySQL thread!