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.htmlhttp://www.postgresql.org/docs/9.5/interactive/infoschema-table-constraints.htmlhttp://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@aklaver.com