Calculation SQL Table size

We have many ways to obtain the size of all tables using SQL Server.

This is a nice solution but we do not really need to use temporary table.

http://therightstuff.de/2007/11/19/How-To-Obtain-The-Size-Of-All-Tables-In-A-SQL-Server-Database.aspx

If we run the script twice, we have an error.

Well, this version uses a variable and we have another solution.

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

Best Practices: Do not run DBCC UPDATEUSAGE routinely. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.

DBCC UPDATEUSAGE(0)

declare @t TABLE 

(

    [name] NVARCHAR(128),

    [rows] CHAR(11),

    reserved VARCHAR(18),

    data VARCHAR(18),

    index_size VARCHAR(18),

    unused VARCHAR(18)

)

 

INSERT @t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

 

SELECT *--, cast(replace(reserved,' KB','' ) as int) as reservedInt

FROM   @t order by cast(replace(reserved,' KB','' ) as int) desc

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 TotalSpaceKB desc

 

Source :

sp_spaceused
https://msdn.microsoft.com/en-us/library/ms188776.aspx

DBCC updateusage
https://msdn.microsoft.com/en-us/library/ms188414.aspx