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.

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.