Search code examples
sqlcountsubquerysnowflake-cloud-data-platformsnowflake-schema

SQL - Find Record count for multiple tables at a time in snowflake

I want to see counts have Tables at 1 time, instead of Running each.

For EX:

select COUNT(*) from  "Fact_MASTER ";
select COUNT(*) from  "Dim_MASTER ";
select COUNT(*) from  "Fact2 ";
select COUNT(*) from  "Dim2";   
select COUNT(*) from  "Fact3";
select COUNT(*) from  "Dim3"

Is there any way we can write a CTE to pull as Record count for each in a temp table or so like below:

enter image description here

Solution

  • You can use union all:

    select 'Fact_MASTER', COUNT(*) from  "Fact_MASTER " union all
select 'Dim_MASTER', COUNT(*) from  "Dim_MASTER " union all
select 'Fact2', COUNT(*) from  "Fact2 " union all
select 'Dim2', COUNT(*) from  "Dim2" union all
select 'Fact3', COUNT(*) from  "Fact3" union all
select 'Dim3', COUNT(*) from  "Dim3"