Get a list of databases that have a certain table

Question

I'm trying to get a list of databases from a Postgresql instance that contains a certain table. I've started using the system catalog tables to try and figure this out. I was trying to use pg_database, pg_class, pg_tables but I couldn't seem to find a way to link pg_class/pg_table back to pg_database to get the database for a given table.

If anyone has any ideas please let me know. It's probably just something stupid that I'm misising.

Thanks in advance.

Answer 1

This is not possible by a single query, because queries cannot span multiple databases, and the list of tables is only contained within the individual databases.

Answer 2

You need to query each database individually to check whether the table is there. Here is an example of a shell script:

for db in $(psql -At -c 'select datname from pg_database where not datistemplate'); do
    psql -d $db -c 'select * from pg_tables where ...'
done