Database and table size quick overview

--
-- go to any specific database and run
SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

if (object_id('tempdb..#t') is not null) drop table #t

-- check
-- http://technet.microsoft.com/en-us/library/ms188776.aspx
-- for data type confirmation
-- 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

select name as TableName
, cast((cast(replace(data, 'KB', '') as decimal(15))+cast(replace(index_size, 'KB', '') as decimal(15)))/1024 as decimal(18)) as SizeInMB
, cast(rows as decimal(11)) as RowsCount
from #t
where rows<>'0'
order by 2 desc

-- # of rows.
SELECT SUM(CAST([rows] AS decimal(30,0))) AS [rows]
FROM   #t

DROP TABLE #t