CODEWITHSUNDEEP
sqldatabasepostgresql
RadiantHex
RadiantHex

Reputation: 25567

How to get list of all objects? - PostgreSQL

I need a list of the objects included in the db: tables, sequences, etc...

Getting the list of tables was the only thing I was able to find out.

Any ideas for what I could use in order to obtain everything?

Upvotes: 9

Views: 51017

Answers (2)

Jiří Baum
Jiří Baum

Reputation: 6930

Another way would be to use pg_dump with the "schema only" option

pg_dump --schema-only databasename > fname.sql

That will give you definitions in SQL, how the objects might have been created.

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171451

You can do this using INFORMATION_SCHEMA tables, as well as the system catalog.

http://www.alberton.info/postgresql_meta_info.html

E.g.,

List Sequences

SELECT relname
FROM pg_class
WHERE relkind = 'S'
AND relnamespace IN (
    SELECT oid
    FROM pg_namespace
    WHERE nspname NOT LIKE 'pg_%'
    AND nspname != 'information_schema'
);

List Triggers

SELECT trg.tgname AS trigger_name
  FROM pg_trigger trg, pg_class tbl
 WHERE trg.tgrelid = tbl.oid
   AND tbl.relname !~ '^pg_';
-- or
SELECT tgname AS trigger_name
  FROM pg_trigger
 WHERE tgname !~ '^pg_';

-- with INFORMATION_SCHEMA:

SELECT DISTINCT trigger_name
  FROM information_schema.triggers
 WHERE trigger_schema NOT IN
       ('pg_catalog', 'information_schema');

Upvotes: 13

Related Questions