Find All Tables With No Primary Key

Jan 28, 2008 / By Sheeri Cabral

Tags:

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;

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

  • Jay Pipes says:

    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.

  • Stoner says:

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

  • Samit Katiyar says:

    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

  • Sheeri Cabral says:

    Samit,

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

  • Sathish says:

    Hi,

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

  • Glanzer says:

    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;’

  • Glanzer says:

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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>