script to find out DB size in Snowflake database

 

SELECT

    USAGE_DATE,

    DATABASE_NAME,

    ROUND(AVERAGE_DATABASE_BYTES / POWER(1024, 3), 2) AS DATABASE_GB,

    ROUND(AVERAGE_FAILSAFE_BYTES / POWER(1024, 3), 2) AS FAILSAFE_GB,

    ROUND(AVERAGE_HYBRID_TABLE_STORAGE_BYTES / POWER(1024, 3), 2) AS HYBRID_TABLE_GB,

    ROUND(AVERAGE_ARCHIVE_STORAGE_COOL_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COOL_GB,

    ROUND(AVERAGE_ARCHIVE_STORAGE_COLD_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COLD_GB,

    ROUND(AVERAGE_ARCHIVE_STORAGE_COOL_FAILSAFE_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COOL_FAILSAFE_GB,

    ROUND(AVERAGE_ARCHIVE_STORAGE_COLD_FAILSAFE_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COLD_FAILSAFE_GB

FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY

ORDER BY USAGE_DATE DESC, DATABASE_NAME;

SELECT

    USAGE_DATE,

    DATABASE_NAME,

    ROUND(AVERAGE_DATABASE_BYTES / POWER(1024, 3), 2) AS DATABASE_GB,

    ROUND(AVERAGE_DATABASE_BYTES / POWER(1024, 2), 2) AS DATABASE_MB

FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY

ORDER BY USAGE_DATE DESC, DATABASE_NAME;

can we create the 2 object with same name in the Snowflake?

 we can create the same name table in Snowflake database.

we create permanent table, temporary table in same  database, But when we query the table temporary table takes priority.

create permanent table  in public schema  in SALES_DB

CREATE  TABLE CUSTOMERS

(

    ID INT,

    NAME STRING,

    CITY STRING

);

create temporary  table  in public schema  in SALES_DB

CREATE   temporary TABLE CUSTOMERS

(

    ID INT,

    NAME STRING,

    CITY STRING

);

insert some records into temporary table ,when you not mention the schema name  by default it will insert to temporary table

INSERT INTO CUSTOMERS (ID, NAME, CITY)

VALUES

    (1, 'John', 'New York'),

    (2, 'Alice', 'London'),

    (3, 'Bob', 'Chennai');

select count(*) from  public.CUSTOMERS; --0 records --permanent table

select count(*) from CUSTOMERS; --3 temp records

we can conclude that temporary table will take the precedence or priority when we use same name table in Snowflake database.

Snowflake Database Admin scripts

 Snowflake DB Admin scripts:

Snowflake DB admin commands/scripts

***************************************

To know the current_user

SELECT CURRENT_USER();

To know the current_Role

SELECT CURRENT_ROLE();

To know the current_DB

SELECT CURRENT_DATABASE();

To use another DB

use DWDB1

To list down the DB's

SHOW DATABASES;

select * from information_schema.databases;

To list down the tables,views,users

show tables;

SHOW WAREHOUSES;

SHOW USERS;

show views;

show functions;

show procedures;

SHOW SCHEMAs;

show roles;