×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sum Column Widths For Table

Sum Column Widths For Table

Sum Column Widths For Table

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

CODE

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

RE: Sum Column Widths For Table

Have you tried

CODE --> sql

sp_columns 'Customer' 
?

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

RE: Sum Column Widths For Table

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

CODE

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) 

CODE

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

RE: Sum Column Widths For Table

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close