计算表记录的最大存储大小? [英] Calculate the maximum storage size of table record?
问题描述
有没有一种方法可以确定在 SQL Server 中手动完成后记录的最大大小是多少?例如:
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
)
所以,如果我没记错的话,当手动计算该记录时,该记录最多可以是 272 字节
.但是,我有一个包含更多列的表,我需要对多个表执行此操作,因此我想知道是否可以通过简单查询来执行此操作.
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.
我在 INFORMATION_SCHEMA.TABLES
甚至 INFORMATION_SCHEMA.COLUMNS
中找不到任何信息,我想我可以在其中执行简单的 SUM
例子.此外,sysobjects
和 syscolumns
似乎没有必要的信息.syscolumns
表确实有一个 length
字段,但这不是实际的存储大小.
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.
谢谢大家!
推荐答案
试试这个:
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
这篇关于计算表记录的最大存储大小?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!