On Fri, Apr 29, 2016 at 2:07 PM, Dustin Kempter <dustink@xxxxxxxxxxxxxxxxxxx> 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.
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.
Maranatha! <><
John McKown
John McKown
