Sql-server – How to count the number of columns in each table

sql serversql-server-2008

I want to write a script to listing the tables in my database with the number of columns in that table.

Like this:

table name      number       
---------       --------     
table1            1         
table2            13        
table3            2         
table4            6    

Best Answer

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;