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?
Add a comment
|
2 Answers
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');
Comments
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.
