Sum Column Widths For Table 1

[Auguy]

I can get the column size of varchar and other character columns with.

SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'Customer'

But it doesn't show the storage size of int, bit, etc., so I can't sum them up.
Is there a way to get the record size including columns like int, smalldatetime, bit, etc?
I'm not looking for the maximum size of the rows in the table, just the designed row size.


Auguy
Sylvania/Toledo Ohio

 

[dhookom]

Have you tried

sp_columns 'Customer'

?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[gmmastros]

instead of information schema, I recommend the sys schema. Something like this....

Select object_name(object_id) As TableName,
       columns.name As ColumnName,
       types.name As DataType,
       columns.max_length As DefinedSize
From   sys.columns 
       Inner Join sys.types
         On columns.system_type_id = types.system_type_id
Where  object_id = object_id('County')

Select object_name(object_id) As TableName,
       Sum(columns.max_length) As DefinedRowSize
From   sys.columns 
       Inner Join sys.types
         On columns.system_type_id = types.system_type_id
Where  object_id = object_id('County')
Group By object_name(object_id)

Select  tables.name As TableName,
        Sum(columns.max_length) As DefinedRowSize
From    sys.tables
        Inner Join sys.columns 
          On Tables.object_id = columns.object_id
        Inner Join sys.types
          On columns.system_type_id = types.system_type_id
Group By tables.name

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks to both of you.

Duane, thanks that got me closer.

George, both procs are really nice, your posts are always great!


Auguy
Sylvania/Toledo Ohio