2

In Snowflake database, I can have a report showing all database names by

SELECT * from information_schema.databases;

In each DB, I can select all schema names by

select catalog_name as database,
       schema_name
from   snowflake.information_schema.schemata
order  by database, schema_name asc;

I want a report to show everyone databases and their schema. How can I do that?

Thank you so much!

dave
  • 296
  • 1
  • 12
  • 26

2 Answers2

2

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)
;
Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
1

You can use ACCOUNT_USAGE views to generate a report containing all DBs with their respective schemas in an account.

select db.database_Name, sch.schema_name  
from SNOWFLAKE.account_usage.databases db 
inner join SNOWFLAKE.account_usage.schemata sch 
   ON  db.database_name = sch.catalog_name 
group by 1,2 order by 1;

Note: Only schmeas which have access granted to current role will be visible. For the whole list of DBs and schemas use ACCOUNTADMIN role.

dave
  • 296
  • 1
  • 12
  • 26
Zonera
  • 178
  • 9
  • Note that information in the SNOWFLAKE database is 45 minutes to 3 hours behind the changes. So if you create or drop databases or schemas, it may not be captured right away in this SQL. – Greg Pavlik Jul 06 '20 at 13:50
  • select db.database_Name, sch.schema_name from SNOWFLAKE.account_usage.databases db join SNOWFLAKE.account_usage.schemata sch ON db.database_name = sch.catalog_name order by 1 desc; ---- I removed group by, no need for it – dave Jul 07 '20 at 02:13
  • I would have added a where clause like this WHERE Sch.DELETED IS NULL and db.DELETED IS NULL instead of a group by. – deutschZuid May 09 '23 at 22:20