Menu
How can I get the size in bytes of a table returned by a SQL query in SSMs?
438
The DATALENGTH() function returns the number of bytes used to represent an expression. Note: The DATALENGTH() function counts both leading and trailing spaces when calculating the length of the expression.
This can be accomplished easily with the following query: SELECT TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.
Are you looking for the size of the table, or the size of a row in the table? The latter is only readily available if all your columns are of fixed size, i.e. nchar and not nvarchar etc.
With var sized columns you can use the maximum length of each column, and sum these, to give you a maximum row size, but this really won't accurately reflect your real row sizes.
select sum(max_length)
from sys.columns
where object_id = object_id('MyTable')
You might also create a query that returns DATALENGTH for each column in any particular row to get the total size of only that row.
178
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
