Find a table in a schema without knowing in advance

Question

Is it possible to easily see what tables exist in what schemas, at a glance?

So far I have had to connect to a database, view the schemas, then change the search path to one of the schemas and then list the tables. I had to do this for multiple schemas until I found the table I was looking for.

What if there is a scenario where you inherit a poorly documented database and you want to find a specific table in hundreds of schemas?

Ideally I imagine some output like so;

 SCHEMA        TABLE
--------------------
schema1       table1
schema2       table2
schema2       table1
--------------------

Or even the more standard <SCHEMA_NAME>.<TABLE_NAME>;

schema1.table1
schema2.table2
schema2.table1

The latter output would be even better since you could simply check the table using copy-paste;

my-database=# \d schema2.table1

Ideally I'm hoping I missed a built-in command to find this. I don't really want to create and memorize a lengthy SQL command to get this (somewhat basic) information.

Answer 1

You can make use of pg_tables

SELECT schemaname, tablename,  
      quote_ident(schemaname) || '.' || quote_ident(tablename) 
FROM pg_tables 
WHERE tablename = 'test';