Snowflake - Helpful Queries

Get row counts for all tables in a DB:

SELECT table_schema
       || '.'
       || table_name AS "table_name",
       row_count
FROM   information_schema.tables
WHERE  table_type = 'BASE TABLE'
ORDER  BY row_count;

Information Schema

• https://docs.snowflake.com/en/sql-reference/info-schema.html#list-of-views

Get row counts for all views in a DB:

It is straightforward to get the number of rows out of the INFORMATION_SCHEMA for tables, but not for views.

Instead, you will need to get the names of all the views out of the INFORMATION_SCHEMA, and then construct a query to run COUNT(*) for each view:

select listagg(xx, ' union all ')
from (
    select 'select count(*) c, \'' || x || '\' v from ' || x as xx
    from (
        select TABLE_CATALOG ||'.'|| TABLE_SCHEMA ||'."'||TABLE_NAME||'"' x
        from  KNOEMA_FORECAST_DATA_ATLAS.INFORMATION_SCHEMA.VIEWS
        where table_schema='FORECAST'
    )
)

The result of that query will look like:

select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABORIGINAL_POP_PROJ"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABORIGINAL_POP_PROJ" 
union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABORIGINAL_POP_PROJ_REMOTE"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABORIGINAL_POP_PROJ_REMOTE" 
union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABS_ABORIGINAL_POPPROJ_INDREGION"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABS_ABORIGINAL_POPPROJ_INDREGION" 
union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABS_FAMILY_PROJ"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABS_FAMILY_PROJ" 
union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABS_HH_PROV"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABS_HH_PROV" 
union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABS_PERSONS_PROJ"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABS_PERSONS_PROJ" 
union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ACT2015"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ACT2015" 
union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."APWSCR2019"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."APWSCR2019" 
union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ASEANEO2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ASEANEO2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ASEANEO2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ASEANEO2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."AUSFCRAE2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."AUSFCRAE2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."AUSFCRAE2020Sep"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."AUSFCRAE2020Sep" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."BALEA2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."BALEA2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."BLSIOM2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."BLSIOM2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."BREXRATE2019"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."BREXRATE2019" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CAESD2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CAESD2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CBIMISUCBAPHLE2012"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CBIMISUCBAPHLE2012" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CBOTPEF2020Sep"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CBOTPEF2020Sep" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CBS83558ENG"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CBS83558ENG" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CEAICPS2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CEAICPS2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."COCERALGCF2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."COCERALGCF2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CS2016"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."CS2016" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."DOSIPIX2019MAY"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."DOSIPIX2019MAY" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."DSTHST5"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."DSTHST5" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EAP_2EAP_SEX_AGE_GEO_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EAP_2EAP_SEX_AGE_GEO_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EAP_2EAP_SEX_AGE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EAP_2EAP_SEX_AGE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EAP_2MDN_SEX_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EAP_2MDN_SEX_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EAP_2WAP_SEX_AGE_RT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EAP_2WAP_SEX_AGE_RT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ECBRMP2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ECBRMP2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIAAEO2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIAAEO2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIAAEO2021"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIAAEO2021" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIASTEO2021JAN"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIASTEO2021JAN" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIP_2EET_SEX_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIP_2EET_SEX_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIP_2EIP_SEX_AGE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIP_2EIP_SEX_AGE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIP_2PLF_SEX_AGE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIP_2PLF_SEX_AGE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIP_2WAP_SEX_AGE_RT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EIP_2WAP_SEX_AGE_RT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_AGE_STE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_AGE_STE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_AGE_CLA_DT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_AGE_CLA_DT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_AGE_CLA_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_AGE_CLA_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_AGE_GEO_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_AGE_GEO_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_AGE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_AGE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_ECO_DT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_ECO_DT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_ECO_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_ECO_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_GEO_ECO_DT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_GEO_ECO_DT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_GEO_ECO_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_GEO_ECO_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_GEO_OCU_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_GEO_OCU_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_GEO_STE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_GEO_STE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_OCU_DT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_OCU_DT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_OCU_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_OCU_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_STE_DT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_STE_DT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_STE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2EMP_SEX_STE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2WAP_SEX_AGE_RT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EMP_2WAP_SEX_AGE_RT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EO103_LTB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EO103_LTB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EO107_INTERNET_1"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EO107_INTERNET_1" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EO107_INTERNET_2"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EO107_INTERNET_2" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EPIPHS2015"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."EPIPHS2015" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAAAFUSRC2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAAAFUSRC2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGBCR2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGBCR2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGCOS2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGCOS2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGCR2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGCR2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGEF2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGEF2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGMAS2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGMAS2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGMM2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGMM2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGSF2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAOEMAGSF2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAPRIAGR2020AUG"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAPRIAGR2020AUG" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAPRIAGR2020JUN"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FAPRIAGR2020JUN" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FSS2014"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."FSS2014" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."GDP_205U_NOC_NB_R"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."GDP_205U_NOC_NB_R" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."GDP_211P_NOC_NB_R"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."GDP_211P_NOC_NB_R" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."GEI2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."GEI2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."GL_BEXSAT1"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."GL_BEXSAT1" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ICCOMEODFPR"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ICCOMEODFPR" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IDPOPULAN2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IDPOPULAN2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IEAOMR2020Aug"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IEAOMR2020Aug" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IEAOMR2020Oct"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IEAOMR2020Oct" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IEAOMR2020Sep"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IEAOMR2020Sep" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IFDEA2015"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IFDEA2015" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFAPDREO2020Apr"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFAPDREO2020Apr" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFFM2020Oct"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFFM2020Oct" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFOWEO2020Oct"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFOWEO2020Oct" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFOWEO2021Jan"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFOWEO2021Jan" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFWEO2020Oct"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IMFWEO2020Oct" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."INPHAP2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."INPHAP2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."INVPIONRR2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."INVPIONRR2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IS_DCIS_PREVDEM1"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."IS_DCIS_PREVDEM1" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."JPLSBCS2017"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."JPLSBCS2017" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."KOSISKRDEM2019"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."KOSISKRDEM2019" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."LUU_2LU2_SEX_AGE_GEO_RT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."LUU_2LU2_SEX_AGE_GEO_RT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."LUU_2LU4_SEX_AGE_GEO_RT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."LUU_2LU4_SEX_AGE_GEO_RT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."LUU_2LU4_SEX_AGE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."LUU_2LU4_SEX_AGE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."LUU_2LU4_SEX_AGE_RT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."LUU_2LU4_SEX_AGE_RT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."MOSPDSI2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."MOSPDSI2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."MYPLF2017"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."MYPLF2017" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."NDCTAIPOP2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."NDCTAIPOP2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."NSS2014"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."NSS2014" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."OCEDUTT2019"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."OCEDUTT2019" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."OCRD2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."OCRD2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."OPECOSDEP2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."OPECOSDEP2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."OPECOSDEP2021Jan"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."OPECOSDEP2021Jan" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."PAG"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."PAG" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POPPROJ"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POPPROJ" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POPULATION_CLOCK_FY"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POPULATION_CLOCK_FY" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_2LDR_NOC_RT_R"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_2LDR_NOC_RT_R" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_2POP_GEO_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_2POP_GEO_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_2POP_SEX_AGE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_2POP_SEX_AGE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_PROJ"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_PROJ" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_PROJ_2011"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."POP_PROJ_2011" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."RBIHINRR2016"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."RBIHINRR2016" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."SDG_0111_SEX_AGE_RT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."SDG_0111_SEX_AGE_RT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."SDG_0852_SEX_AGE_RT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."SDG_0852_SEX_AGE_RT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."SWENGAUG2019"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."SWENGAUG2019" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."TXECFR2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."TXECFR2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."TXERSVR2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."TXERSVR2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."UNE_2EAP_SEX_AGE_RT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."UNE_2EAP_SEX_AGE_RT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."UNE_2UNE_SEX_AGE_NB"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."UNE_2UNE_SEX_AGE_NB" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."UNWUP2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."UNWUP2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBLONPROSEP2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBLONPROSEP2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBOTFP2020Sep"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBOTFP2020Sep" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDESTATSTAB2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDESTATSTAB2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDPROJOCT2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDPROJOCT2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDREVN2020Sep"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDREVN2020Sep" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDREVNAUG2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDREVNAUG2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDSPENDPJSEP2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCBUDSPENDPJSEP2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCECOPRO2020Jul"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCECOPRO2020Jul" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCECOPRO2021Feb"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCECOPRO2021Feb" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCLPR2019Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCLPR2019Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCOVIDHONPJ2020NOV"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USCOVIDHONPJ2020NOV" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDACPFC2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDACPFC2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAIME2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAIME2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAIME2021"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAIME2021" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAPSD2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAPSD2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAPSD2020RDec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAPSD2020RDec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAPSD2021Jan"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAPSD2021Jan" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDARAYP2017"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDARAYP2017" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAWGST2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USDAWGST2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USEDO2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USEDO2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USEMS2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USEMS2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USFTR2017"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USFTR2017" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USNEMO2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USNEMO2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWETPOP2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWETPOP2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHBBA2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHBBA2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOCIOBSM2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOCIOBSM2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOFD2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOFD2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOMSRS2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOMSRS2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOOBEMRDP2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOOBEMRDP2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOOPI2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."USWHOOPI2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBCB2020OCT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBCB2020OCT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBDB2019"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBDB2019" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBEDS2017Jun"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBEDS2017Jun" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBFUTUREGCM2019"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBFUTUREGCM2019" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBGEPGC2021Jan"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBGEPGC2021Jan" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBIDS2018"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBIDS2018" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBPEP2018Oct"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WBPEP2018Oct" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WESP2021"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WESP2021" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WHOGHO2020OCT"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WHOGHO2020OCT" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WHTDRCOP2020Dec"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WHTDRCOP2020Dec" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WHTDRCOP2020Nov"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WHTDRCOP2020Nov" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WHTDRCOP2021Jan"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WHTDRCOP2021Jan" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WLDBPEOCDP2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WLDBPEOCDP2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WOERDP2020"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WOERDP2020" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WOERDP2021"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WOERDP2021" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WSSRO2020Oct"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WSSRO2020Oct" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WTTC2019"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."WTTC2019" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."dbsamid"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."dbsamid" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."drrskf"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."drrskf" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."elnqwwd"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."elnqwwd" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."fbjgwqg"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."fbjgwqg" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."hznlzr"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."hznlzr" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ichpxy"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ichpxy" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."nstmflb"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."nstmflb" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."tps00002-20170615"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."tps00002-20170615" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."tps00153"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."tps00153" union all select count(*) c, 'KNOEMA_FORECAST_DATA_ATLAS.FORECAST."tps00200-20190703"' v from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."tps00200-20190703"

Now you need to execute that query: Either via copy paste, or use a stored procedure.

If you want the results sorted, add a order by c desc at the end.

Source: https://stackoverflow.com/questions/66116474/how-to-find-the-number-of-rows-for-all-views-in-a-schema/66116475

Run multiple queries

Next to the run button, check mark All Queries to execute all the queries in a single worksheet.

Run SQL Queries in Parallel

Open multiple worksheets, fill them with queries and run all of them. (If needed, check mark the All Queries box in each worksheet.)

Transfer tables from Snowflake to AWS S3

    COPY INTO <S3_LOCATION>
         FROM <DB_NAME>.<SCHEMA_NAME>.<TABLE_NAME>
  CREDENTIALS = (aws_key_id='XXXX' aws_secret_key='XXXX')
  FILE_FORMAT = (TYPE=PARQUET COMPRESSION=SNAPPY)
       HEADER = TRUE
MAX_FILE_SIZE = 5000000000;

We can also specify a subquery for FROM instead of <DB_NAME>.<SCHEMA_NAME>.<TABLE_NAME>.

Scroll further down to know how to deal with TIMESTAMP_TZ and TIMESTAMP_LTZ formats while unloading as parquet.

List details of all columns in a DB

select t.table_catalog as database_name,
       t.table_schema as schema_name,
       t.table_name as table_name,
       t.table_type as table_type,
       c.column_name,
       c.data_type,
       case when c.character_maximum_length is not null
            then c.character_maximum_length
            else c.numeric_precision end as max_length,
       coalesce(c.numeric_scale, null) as num_precision,
       is_nullable
from information_schema.tables t
left join information_schema.columns c 
              on t.table_schema = c.table_schema 
              and t.table_name = c.table_name
where t.table_schema != 'INFORMATION_SCHEMA'
order by schema_name,
         table_name;

Modified from: https://dataedo.com/kb/query/snowflake/list-views-columns

List tables with their primary keys (PKs) in Snowflake

select tab.table_schema,
       tab.table_name,
       tco.constraint_name,
       tco.constraint_type
from information_schema.tables tab
left join information_schema.table_constraints tco
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
order by table_schema,
         table_name;

Snowflake doesn’t allow us to simple list PKs columns by one query. To see tables PK you can use following command:

describe table table_name;

Source: https://dataedo.com/kb/query/snowflake/list-tables-with-their-primary-keys

CREATE TABLE … LIKE

Creates a new table with the same column definitions as an existing table, but without copying data from the existing table. Column names, types, defaults, and constraints are copied to the new table:

CREATE [ OR REPLACE ] TABLE <table_name> LIKE <source_table>
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ COPY GRANTS ]
  [ ... ]

Using IF NOT EXISTS along with LIKE

CREATE TABLE <DB.SCHEMA.TABLE> IF NOT EXISTS LIKE <DB.SCHEMA.TABLE>;

Clone a table

Creates a new table with the same column definitions and containing all the existing data from the source table, without actually copying the data. This variant can also be used to clone a table at a specific time/point in the past (using Time Travel):

CREATE [ OR REPLACE ] TABLE <name> CLONE <source_table>
  [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
  [ COPY GRANTS ]
  [ ... ]

Select the list of columns in a table without using INFORMATION_SCHEMA

The most obvious way would be using the information_schema:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='<TABLE_NAME>';

However you have another way of obtaining the desired list of columns, using the following two steps:

SHOW COLUMNS IN TABLE <TABLE_NAME>;
SELECT * FROM TABLE(RESULT_SCAN(last_query_id()));

Source: https://community.snowflake.com/s/article/Select-the-list-of-columns-in-the-table-without-using-information-schema

How to: Unload TIMESTAMP_TZ and TIMESTAMP_LTZ data to a parquet file

Issue: When we try to unload the data from a table, which has TIMESTAMP_TZ and LTZ columns, to a Parquet file, we hit the below error for these 2 columns:
Error encountered when unloading to PARQUET: TIMESTAMP_TZ and LTZ types are not supported for unloading to Parquet. value get: TIMESTAMP_TZ

Solution: This issue can be fixed by manually casting these columns to String data type while unloading the data through COPY INTO query transformation method, when you are not concerned about the parquet data file schema for the Timestamp column.
Here is an example, where a table which has 2 columns abc(string) and timestamp(TIMESTAMP_TZ(9)). The timestamp column can be unloaded with the below COPY INTO statement to a Parquet file:

copy into @~/parquet/new_parquet from
(
select abc,timestamp::string from t1
)
file_format=(type=parquet, compression=SNAPPY)
;

Source: https://community.snowflake.com/s/article/How-To-Unload-Timestamp-data-in-a-Parquet-file