When I do a \dt in psql I only get a listing of tables in the current schema (public by default).
How can I get a list of all tables in all schemas or a particular schema?
When I do a \dt in psql I only get a listing of tables in the current schema (public by default).
How can I get a list of all tables in all schemas or a particular schema?
On
You can select the tables from information_schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'public'
On
Alternatively to information_schema it is possible to use pg_tables:
select * from pg_tables where schemaname='public';
On
For those coming across this in the future:
If you would like to see a list of relations for several schemas:
$psql mydatabase
mydatabase=# SET search_path TO public, usa; #schema examples
SET
mydatabase=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | counties | table | postgres
public | spatial_ref_sys | table | postgres
public | states | table | postgres
public | us_cities | table | postgres
usa | census2010 | table | postgres
On
If you are interested in listing all tables in a particular schema, I found this answer relevant :
SELECT table_schema||'.'||table_name AS full_rel_name
FROM information_schema.tables
WHERE table_schema = 'yourschemaname';
In all schemas:
In a particular schema:
It is possible to use regular expressions with some restrictions