CODEWITHSUNDEEP
snowflake-schemasnowflake-cloud-data-platform
petemdonnellygooglemailcom
petemdonnellygooglemailcom

Reputation: 1

In Snowflake can I list all databases and their sizes?

How can I list all database along with the size in Snowflake.

The closest I can see is from this Snowflake document DATABASE_STORAGE_USAGE_HISTORY

select *
from table(information_schema.database_storage_usage_history(dateadd('days',-10,current_date()),current_date()));

All I want is a list like this

DatabaseName Size(GB)

MyDB1 10.5

HisDB2 21.3

HerDB3 17.8

Thanks,

Pete

Upvotes: 0

Views: 2271

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11066

This will be up to 3 hours late, since it's shared back from Snowflake as a data share. There are other ways to get the information, but for current state, the TABLE_STORAGE_METRICS provides more information on time travel, failsafe, etc.

select      DATABASE_NAME
           ,sum(ACTIVE_BYTES / 1000000000) ACTIVE_GB
           ,sum(TIME_TRAVEL_BYTES / 1000000000) TIME_TRAVEL_GB
           ,sum(FAILSAFE_BYTES / 1000000000) FAILSAFE_GB
           ,sum(RETAINED_FOR_CLONE_BYTES / 1000000000) RETAINED_FOR_CLONE_GB
           ,ACTIVE_GB+TIME_TRAVEL_GB+FAILSAFE_GB+RETAINED_FOR_CLONE_GB TOTAL_GB
from SNOWFLAKE.ACCOUNT_USAGE.DATABASES D
    left join SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS T
        on D.DATABASE_ID  = T.TABLE_CATALOG_ID
where   D.DELETED is null
group by DATABASE_NAME
order by DATABASE_NAME 
;

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175964

Using DATABASE_STORAGE_USAGE_HISTORY:

This Account Usage view can be used to query the average daily storage usage, in bytes, for databases in the account for the last 365 days (1 year). The data includes:

SELECT *
FROM snowflake.account_usage.DATABASE_STORAGE_USAGE_HISTORY;

Related: Differences Between Account Usage and Information Schema

Upvotes: 1

Related Questions