Can I get all schemas through SQL query?

Question

Is there a way to get all the schemas that exist on the DB server?

select * from information_schema.schemata but even though it has a column catalog_name you will never see a value that id different from the current database in there. Postgres does not support cross-database queries — user330315
– user330315, Commented Apr 5, 2016 at 7:34
and how to do this? I dev application in Java and have to get all schemas from specified database :c — Vladlen Gladis
– Vladlen Gladis, Commented Apr 5, 2016 at 7:41
Maybe you can achieve what you need with a combination of shell scripting and psql command. Can you expand on your requirements, type of output, and the consumer of the data? — Ezequiel Tolnay
– Ezequiel Tolnay, Commented Apr 5, 2016 at 8:55

Vladlen Gladis · Accepted Answer · 2019-05-20 07:12:08Z

1

SELECT schema_name 
FROM information_schema.schemata 
WHERE schema_name NOT LIKE 'pg%'
AND schema_name NOT LIKE 'information_schema'
AND catalog_name LIKE 'YOUR_DATABASE_NAME'

Two NOT LIKE clauses are to avoid system schemas

edited May 20, 2019 at 7:12

answered May 10, 2016 at 7:18

Vladlen Gladis

1,7875 gold badges20 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can I get all schemas through SQL query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related