Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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    
share|improve this question
up vote 14 down vote accepted

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.