List tables with the largest number of columns in Snowflake

Query that returns tables with number of columns, ordered from the ones that have the most.

Query

select t.table_schema || '.' ||  t.table_name as table_name,
count(*) as columns
from information_schema.tables t
inner join information_schema.columns c on 
       c.table_schema = t.table_schema and c.table_name = t.table_name
where t.table_type = 'BASE TABLE'     
group by t.table_schema, t.table_name   
order by count(*) desc; 

Columns

  • table_name - name of the table (with schema name)
  • columns - number of columns in table

Rows

  • One row represents one table in a database
  • Ordered by number of columns descending - from tables with the most columns

Sample results

0
There are no comments. Click here to write the first comment.