How do I count the number of columns in each table?
You can look at columns in sys.columns:
Returns a row for each column of an object that has columns, such as views or tables. The following is a list of object types that have columns:
- Table-valued assembly functions (FT)
- Inline table-valued SQL functions (IF)
- Internal tables (IT)
- System tables (S)
- Table-valued SQL functions (TF)
- User tables (U)
- Views (V)
For columns count in tables, this query can be used:
SELECT [Schema] = s.name
, [Table] = t.name
, number = COUNT(*)
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.name, s.name
- sys.tables can be replaced by sys.views for count in views
sys.objects can also be used with a
WHERE
clause on required type(s):SELECT [Schema] = s.name , [Table] = o.name , number = COUNT(*) , o.type_desc FROM sys.columns c INNER JOIN sys.objects o ON c.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE type IN ( 'U' --> U = Table (user-defined) , 'V' --> V = View , ...) GROUP BY o.name, s.name, o.type_desc;
This would work too although it is preferable (read The case against INFORMATION_SCHEMA views from Aaron Bertrand) to use the first query:
SELECT TABLE_SCHEMA
, TABLE_NAME
, number = COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME;