List all tables from a specific database

[Peter_in_2780]

Feeding "postgresql list tables in database" into your search engine will produce a zillion useful answers.

Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012

[_Flaviu]

Yes, I did. But I haven't found any SQL to tell me the tables for a specific database only.

[k5054]

You can get Postgres to tell you how to do that using the -E (display hidden queries) option to psql. So for example

$ psql -E -l
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                              List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+-----------+-------------+-------------+-----------------------
template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | ebacon | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 |
(3 rows)

Likewise for the \dt command inside psql. Then all you need to do is to figure out how to combine both queries into one. Keep Calm and Carry On

[_Flaviu]

Thank you. That query retrieve all databases. And for all tables from a database only ?

[k5054]

try psql -E test -c "\dt". You can then take that output and combine with the previous one to get a listing of all tables across all databases. Keep Calm and Carry On

[_Flaviu]

"get a listing of all tables across all databases" That I already did. But doesn't help me, because I need the tables below a specific database, not all tables from all databases.

[Lost User]

PostgreSQL Show Tables[^]

[_Flaviu]

Thank you Richard. I have tried before, and now, the SQL from that page:

SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

And as result I got all tables, from ALL databases. Untitled6 — ImgBB[^]

[Lost User]

But that is exactly what that command is supposed to do. You need to follow the instructions from the top of the page.

[_Flaviu]

I am pretty close. And what SQL command should I use to select the database, and after that to call SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' ? I have tried:

USE database my_database

but seem to not work:

ERROR: syntax error at or near "USE";
Error while executing the query

You know, I need to all this programatically, using SQL commands, not using Postgre tools.

[Lost User]

Sorry, I do not have a system available to test this. However, I do not see any instruction concerning a USE statement in the tutorial on that page.

[Sakshi Jain 2022]

1. Using SQL Query To show the list of tables with the corresponding schema name, run this statement:

SELECT * FROM information_schema.tables;

or in a particular schema:

SELECT * FROM information_schema.tables WHERE table_schema = 'schema_name';

2. Using psql To list all tables: In all schemas:

\dt *.*

In a particular schema:

\dt schema_name.*