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

Question

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:

Answer 1

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"

Answer 2

It looks like you want each count in a separate column. If so, you can turn each query to a separate subquery, and select them:

select 
    (select count(*) from "Fact_MASTER") as fact_master,
    (select count(*) from "Dim_MASTER ") as dim_master,
    (select count(*) from "Fact2")       as fact2,
    (select count(*) from "Dim2")        as dim2,
    (select count(*) from "Fact3")       as fact3
    (select count(*) from "Dim3")        as dim3

Answer 3

Have you tried simply running:

SHOW TABLES;

If you then want to use that information for something else, you can then follow-up with something like:

select "rows" as cnt
from table(result_scan(last_query_id()))
where "name" in (...);

If you have a set list of tables that you want to PIVOT you can also use the result_scan() function to pivot the data:

https://docs.snowflake.com/en/sql-reference/constructs/pivot.html