List tables with the largest number of columns in SQL Server database

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


select schema_name(tab.schema_id) + '.' + as [table], 
       count(*) as [columns]
   from sys.tables as tab
        inner join sys.columns as col
            on tab.object_id = col.object_id
group by schema_name(tab.schema_id),
order by count(*) desc


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


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

Sample results

Here is a statistic from tables in Dataedo repository. Quite a usual database.

And this, by comparison is a result from demo Microsoft Dynamics NAV database. As you can see it is considerably more complex.

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