Snowflake: Get list of all DB and schemas

To get list of all Snowflake Databases:

show databases ;

To get list of all schemas in a database:

show schemas ; 

But issue with above is we cant get list of all schemas in the whole account or from all databases.

To get all schemas in an account, you can do this (note that it only reports on what the current role has privileges on):

show schemas in account;

If you want to filter the results, you can use the result_scan immediately after running the show, which is a metadata query. It may look something like this:

select "database_name" as DATABASE_NAME
"name" as SCHEMA_NAME
from table(result_scan(last_query_id()))
where SCHEMA_NAME not in ('INFORMATION_SCHEMA') -- optional filter(s)
;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s