Calculate the maximum storage size of table record?

sql, sql-server, sql-server-2005

Solution

Try this:

Select  schema_name(T.schema_id) As SchemaName,
        T.Name As TableName,
        Sum(C.max_length) As RowSize
From    sys.tables T
        Inner Join sys.columns C
            ON T.object_id = C.Object_ID
        INNER JOIN sys.types S
            On C.system_type_id = S.system_type_Id
Group By schema_name(T.schema_id),
        T.Name
Order By schema_name(T.schema_id),
        T.Name

Problem

Is there a way to determine what the maximum size of a record would be in SQL Server past doing it by hand? For example: ``` CREATE TABLE test ( id INT PRIMARY KEY IDENTITY(1, 1), name VARCHAR(256), test_date DATETIME ) ``` so, if I'm not mistaken, when calculating that by hand that record could be a maximum of `272 bytes`. However, I have a table with a lot more columns than that, and I need to do this for more than one table, so I wanted to know if I could do this with a simple query. I can't find any information in `INFORMATION_SCHEMA.TABLES` or even `INFORMATION_SCHEMA.COLUMNS` where I figured I could do a simple `SUM` for example. Further, `sysobjects` and `syscolumns` don't seem to have the necessary information. The `syscolumns` table does have a `length` field but that's not the actual storage size. Thanks all!

Original source