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.

1
2DBCC UPDATEUSAGE(0)

 1
 2declare @t TABLE
 3
 4(
 5
 6    [name] NVARCHAR(128),
 7
 8    [rows] CHAR(11),
 9
10    reserved VARCHAR(18),
11
12    data VARCHAR(18),
13
14    index_size VARCHAR(18),
15
16    unused VARCHAR(18)
17
18)
19
20INSERT @t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
21
22SELECT *--, cast(replace(reserved,' KB','' ) as int) as reservedInt
23
24FROM   @t order by cast(replace(reserved,' KB','' ) as int) desc

 1
 2SELECT
 3
 4    t.NAME AS TableName,
 5
 6    s.Name AS SchemaName,
 7
 8    p.rows AS RowCounts,
 9
10    SUM(a.total_pages) * 8 AS TotalSpaceKB,
11
12    SUM(a.used_pages) * 8 AS UsedSpaceKB,
13
14    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
15
16FROM
17
18    sys.tables t
19
20INNER JOIN
21
22    sys.indexes i ON t.OBJECT_ID = i.object_id
23
24INNER JOIN
25
26    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
27
28INNER JOIN
29
30    sys.allocation_units a ON p.partition_id = a.container_id
31
32LEFT OUTER JOIN
33
34    sys.schemas s ON t.schema_id = s.schema_id
35
36WHERE
37
38    t.NAME NOT LIKE 'dt%'
39
40    AND t.is_ms_shipped = 0
41
42    AND i.OBJECT_ID > 255
43
44GROUP BY t.Name, s.Name, p.Rows
45
46ORDER 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