Is it possible to find out how big the data is in KB or MB for individual columns in a table? I have a script which tells me the physical size of each table, but I would like to know how much of that is taken up by certain columns in the database, especially when I have XML stored in a column.
是否有可能找出一个表中单个列在KB或MB中的数据有多大?我有一个脚本，它告诉我每个表的物理大小，但是我想知道数据库中的某些列占用了多少空间，特别是当我将XML存储在列中时。
Any help much appreciated
任何帮助的感谢
Cheers
干杯
2 个解决方案
#1
20
You should be able to use the datalength function, something like
您应该能够使用datalength函数，比如
select sum(datalength(yourfield))
from yourtable
This will summate the datalengths of all the entries of that field in the table - it will not account for overheads such as variable length field pointers, space in the nullability bitmap etc.
这将与表中该字段的所有条目的数据集相加——它将不考虑开销，如可变长度字段指针、可空位图中的空间等。
#2
1
Select SUM(DATALENGTH(columnsA)) / 1024.0 AS KB
选择SUM(DATALENGTH(columnsA))) / 1024.0作为KB
#1
20
You should be able to use the datalength function, something like
您应该能够使用datalength函数，比如
select sum(datalength(yourfield))
from yourtable
This will summate the datalengths of all the entries of that field in the table - it will not account for overheads such as variable length field pointers, space in the nullability bitmap etc.
这将与表中该字段的所有条目的数据集相加——它将不考虑开销，如可变长度字段指针、可空位图中的空间等。
#2
1
Select SUM(DATALENGTH(columnsA)) / 1024.0 AS KB
选择SUM(DATALENGTH(columnsA))) / 1024.0作为KB