8

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    
20

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

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TBlName'

Thats Enough to Get a number of columns that we have in to our Table

Its only for One table

| improve this answer | |
  • Your answer should consider CATALOG and SCHEMA too. – McNets Feb 28 '18 at 15:38
0
select st.name, count(1) as column_count 
from sys.tables st
inner join sys.columns sc on st.object_id = sc.object_id
where st.name like '%%'
group by st.name
order by count(1) desc
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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