How to merge two tables with different column number in Snowflake?

Question

I am querying TABLE_SCHEMA,TABLE_NAME,CREATED,LAST_ALTERED columns from Snowflake information schema. VIEWS. Next, I would like to MERGE that table with row count for the view. Below are my queries I am running in Snowflake my issue is I am not sure how to combine these two table in 1 table ?

Note: I am new to Snowflake. Please provide code with explanation.

Thanks in advance for help!

Query 1

 SELECT TABLE_SCHEMA,TABLE_NAME,CREATED,LAST_ALTERED FROM DB.SCHEMA.VIEWS
WHERE TABLE_SCHEMA="MY_SHEMA" AND TABLE_NAME IN ('VIEW_TABLE1','VIEW_TABLE2','VIEW_TABLE3')

Query 2

SELECT COUNT(*) FROM DB.SCHEMA.VIEW_TABLE1
UNION ALL SELECT COUNT(*) FROM DB.SCHEMA.VIEW_TABLE2

Answer 1

Include table/view names as string in your count(*) queries and then you can join.

Example below -

select * from
(SELECT TABLE_SCHEMA,TABLE_NAME,CREATED FROM information_schema.tables
WHERE TABLE_SCHEMA='PUBLIC' AND TABLE_NAME IN ('D1','D2')) t1
left join
(
SELECT 'D1' table_name, COUNT(*) FROM d1
UNION ALL SELECT 'D2',COUNT(*) FROM d2) t2
on t1.table_name = t2.table_name ;

TABLE_SCHEMA	TABLE_NAME	CREATED	TABLE_NAME	COUNT(*)
PUBLIC	D1	2022-04-06 14:24:56.224 -0700	D1	12
PUBLIC	D2	2022-04-06 14:25:27.276 -0700	D2	5