How to find the number of rows for all views in a schema?

Question

Sometimes I have access to schemas with hundreds of views (when using the Snowflake Data Marketplace). How can I find the number of rows for each of these views?

What I need is an efficient way to do something like:

select count(*)
from KNOEMA_FORECAST_DATA_ATLAS.FORECAST."ABORIGINAL_POP_PROJ";

... but for all views in that Knoema schema.

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2021-02-09T09:46:38

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.

The results will look like:

If you want to add extra information, like the description of each view out of the information schema, you could augment the generated query:

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

And the results of running the resulting query would look like:

How to find the number of rows for all views in a schema?

1 Answers