How does sp_spaceused calculate the size of tables with BLOB / overflow / out-of-row types?

Question

My requirement is to calculate table sizes in my SQL Server Database.

For this, I am using the predefined SP, sp_spaceused. But, if I have BLOB type columns in my table (like varbinary(max) or FILESTREAM varbinary(max)), how is the size computed?

• CASE 1: If the table has varbinary(max) column, is the size inclusive of the binary data?

• CASE 2: If the table contains FILESTREAM varbinary(max) type column, then is the size inclusive of the binary data? This case arises because here the binary data is stored in the Windows File System.

Can you please help me on this?

Answer 1

I think that your question would be better answered in dba stackexchange. However, I'm using the following SQL command to obtain the table sizes:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY t.Name

It works for the CASE 1, however, I'm unsure about the second case.