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?
objects system catalog view. We can use system catalog view sys. objects to view all objects in a SQL database. It has a column type that contains the object category.
Use \l or \l+ in psql to show all databases in the current PostgreSQL server. Use the SELECT statement to query data from the pg_database to get all databases.
To list down all tables columns on a specific table in the a PostgreSQL database using psql command-line, you can use \dS your_table_name.
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');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With