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.
Thanks in advance!
Please, ALWAYS provide Postgresql version & O/S, regardless of whether you think it is pertinet.
Now try this:
SELECT n.nspname, c.relname as table FROM pg_class c JOIN pg_namespace n ON (n.oid =c.relnamespace ) WHERE relkind = 'r' AND relname NOT LIKE 'pg_%' AND relname NOT LIKE 'sql_%' AND relhaspkey = FALSE ORDER BY n.nspname, c.relname;
That gives a list of all tables in all schemas in the database. But this needs to be refined to those without a primary key. Using the -E switch, I looked at the output from \di+ <table>, which will list whether the table has a primary key or not, that command is implemented via multiple SELECT statements which I haven't reviewed yet.
--
Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
--
The unfacts, did we have them, are too imprecisely few to warrant our certitude.