Query that returns tables with number of columns, ordered from the ones that have the most.
select schema_name(tab.schema_id) + '.' + tab.name 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), tab.name 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
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.