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!
source to share
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
source to share