The Size Of All Tables In a SQL Server Database

     Ever wonder how big a table really is in your database? You know there are a million rows in the table, but how much space is that really taking?

   SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes. which might surprise you.

-- DB size.
EXEC sp_spaceused

-- Particular one table size.
sp_spaceused 'Employee'

-- Table row counts and sizes.

CREATE TABLE TableSize
( 
    TableName VARCHAR(150),
    RowsCount CHAR(11),
    Reserved VARCHAR(18), 
    Data VARCHAR(18), 
    Index_Size VARCHAR(18),
    Unused_Size VARCHAR(18)
) 

INSERT TableSize EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

-- Select all table with size

SELECT * FROM TableSize 

-------------------------