On 04/29/2016 11:07 AM, Dustin Kempter wrote:
Hi all,
Is there a query I can run that will scan through all the tables of a
database and give me a list of all tables without a primary key? Im not
having any luck with this.
Two options:
First
http://www.postgresql.org/docs/9.5/interactive/catalog-pg-class.html
select * from pg_class where relhaspkey ='f' and relkind ='r' and
relname not like 'pg_%';
*NOTE* from above link:
"
relhaspkey bool True if the table has (or once had) a primary key
"
So it may not totally reflect current reality.
Second
http://www.postgresql.org/docs/9.5/interactive/information-schema.html
http://www.postgresql.org/docs/9.5/interactive/infoschema-table-constraints.html
http://www.postgresql.org/docs/9.5/interactive/infoschema-tables.html
I restricted the below to exclude system and information_schema tables:
select * from information_schema.tables where table_catalog = 'test' and
table_schema !='pg_catalog' and table_schema != 'information_schema'
and table_name not in(select table_name from
information_schema.table_constraints where constraint_type = 'PRIMARY KEY');
*NOTE* The information returned is dependent on the privileges of the
user running the query, so if you want to see everything run as a superuser.
Thanks in advance!
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
