Calculate maximum table storage size?

Is there a way to determine what the maximum record size will be in the past in SQL Server if done manually? For example:

CREATE TABLE test (
    id INT PRIMARY KEY IDENTITY(1, 1),
    name VARCHAR(256),
    test_date DATETIME
)

      

therefore, if I am not mistaken, when calculating what manually this entry can be the maximum 272 bytes

. However, I have a table with many more columns than this and I need to do this for more than one table, so I wanted to know if I could do it with a simple query.

I can't find any information at INFORMATION_SCHEMA.TABLES

or even INFORMATION_SCHEMA.COLUMNS

where I figured I could do a simple SUM

one for example. Also, sysobjects

and syscolumns

do not seem to have the required information. The table syscolumns

has a margin length

, but not the actual storage size.

Thanks everyone!

+3


source to share


1 answer


Try the following:



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

      

+4


source







All Articles