Check Table Size

--############################1
SELECT 'Database Name: ', DB_NAME()
SET NOCOUNT ON

IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name='##tmp')
BEGIN
DROP TABLE ##tmp
END

CREATE TABLE ##tmp(
     nam varchar(50),
     rows int,
     res varchar(15),
     data varchar(15),
     ind_sze varchar(15),
     unsed varchar(15)
     )

DECLARE @tblname varchar(50)
DECLARE @scmname varchar(50)
DECLARE @sql varchar(100)

DECLARE tbsize CURSOR for
SELECT ss.name, so.name FROM sys.objects so
INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id
WHERE so.type='U'

OPEN tbsize
FETCH NEXT FROM tbsize INTO @scmname, @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'INSERT INTO ##tmp EXEC sp_spaceused '''+@scmname+'.'+@tblname+''''
EXEC (@sql)
FETCH NEXT FROM tbsize INTO @scmname, @tblname
END
CLOSE tbsize
DEALLOCATE tbsize

SELECT nam [Table_Name],
rows [Total_Rows],
convert(int, replace(res, 'KB', ''))/1024 [Total_Table_Size(MB)],
convert(int, replace(data, 'KB', ''))/1024 [Data_size(MB)],
convert(int, replace(ind_sze, 'KB', ''))/1024 [Index_Size(MB)],
convert(int, replace(unsed, 'KB', ''))/1024 [Unused_Space(MB)]
FROM ##tmp
ORDER BY [Total_Table_Size(MB)] DESC

DROP TABLE ##tmp

-- 參考
-- 某老外的blog