How To Obtain The Size Of All Tables In A SQL Server Database

SET NOCOUNT ON 

DBCC UPDATEUSAGE(0) 

-- DB size.

EXEC sp_spaceused

-- Table row counts and sizes.

CREATE TABLE #t 

( 

    [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 *

FROM   #t

-- # of rows. 

SELECT   REPLACE(data,'KB','')  FROM   #t --order by data  

alter table  #t add data_1 numeric(18,2) 

UPDATE   #t SET data_1=convert(numeric(18,2),REPLACE(data,'KB',''))

SELECT *   FROM   #t order by data_1

 

DROP TABLE #t