SELECT table_catalog, table_schema, table_nameThe last line is useful because many predefined tables don't have primary keys.
FROM information_schema.tables
WHERE (table_catalog, table_schema, table_name) NOT IN
(SELECT table_catalog, table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY')
AND table_schema NOT IN ('information_schema', 'pg_catalog');
Since you're using the standardized information schema, this query should be portable to other SQL database systems. The identical query works on MySQL, for example.
Posts
One thing to note is that this will only tell you about tables that you have some privilege on. So if you don't run it as superuser, you might miss some stuff.
ReplyDeleteThis slight modification gets the tables that have no kind of uniqueness on
ReplyDeletethem, whether it's called a primary key or not :)
SELECT table_catalog, table_schema, table_name
FROM information_schema.tables
WHERE (table_catalog, table_schema, table_name) NOT IN
(SELECT table_catalog, table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_type NOT IN 'PRIMARY KEY', 'UNIQUE'))
AND table_schema NOT IN ('information_schema', 'pg_catalog');
Checking further, the above queries find VIEWs along with TABLEs, at least in PostgreSQL, so the following should be better:
ReplyDeleteSELECT table_catalog, table_schema, table_name
FROM information_schema.tables
WHERE (table_catalog, table_schema, table_name) NOT IN
(SELECT table_catalog, table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_type NOT IN ('PRIMARY KEY', 'UNIQUE'))
AND table_schema NOT IN ('information_schema', 'pg_catalog')
AND table_type = 'BASE TABLE';
For me David's queries do not list a table whose (only) constraint is a foreign key reference.
ReplyDeleteHow about this one?
ReplyDeleteselect c.oid::regclass from pg_class c join pg_namespace n on (relnamespace=n.oid) where relkind = 'r' and nspname in ('schema_list') except select indrelid::regclass from pg_index where indisunique ;
@denish got one that goes strictly from the information schema?
ReplyDeleteNice!
ReplyDeleteThanks for useful information.
I think including "engine" column would be useful, so it turns to
SELECT table_catalog, table_schema, table_name, engine
FROM information_schema.tables
WHERE (table_catalog, table_schema, table_name) NOT IN
(SELECT table_catalog, table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY')
AND table_schema NOT IN ('information_schema', 'pg_catalog');
There is no engine column in that view in standard SQL.
DeleteOld post, but still working and very helpful.
ReplyDeleteThank you!