SQL Table Size

Many times you may want to check the size of set of tables in SQL server.  There are multiple ways to achieve the same.

DECLARE @Unit CHAR(2)
DECLARE @SlicerSize FLOAT

SET @Unit = 'GB' --MB

CREATE TABLE #DataSize
(
    RowId        INT IDENTITY,
    TableName    SYSNAME,
    DataRowCount BIGINT,
    Reserved     VARCHAR(20),
    Data         VARCHAR(20),
    IndexSize    VARCHAR(20),
    Unused       VARCHAR(20)
)

SELECT @SlicerSize = CASE WHEN @Unit = 'GB' 
                          THEN 1024.0 / 1024.0
                          ELSE 1024.0 
                     END

-- Add the table you want to check the size
INSERT #DataSize EXEC sp_executesql N'EXEC sp_spaceused [dbo.Emp]'
INSERT #DataSize EXEC sp_executesql N'EXEC sp_spaceused [dbo.Address]'

SELECT TableName, 
       DataRowCount / 1000 AS RowCountInKs ,
       CONVERT(DECIMAL(10,2),CONVERT(INT,REPLACE(Reserved,' KB','')) / @SlicerSize)  AS ReservedSpace ,
       CONVERT(DECIMAL(10,2),CONVERT(INT,REPLACE(Data,' KB','')) / @SlicerSize)      AS DataSize ,
       CONVERT(DECIMAL(10,2),CONVERT(INT,REPLACE(IndexSize,' KB','')) / @SlicerSize) AS IndexSize,
       @Unit                                                                         AS Unit 
       FROM #DataSize

DROP TABLE #DataSize