Size of tables in a SQL Server database

gistfile1.sql

--
-- Count rows in all tables, display tables with rows
--

CREATE TABLE #T (
	name NVARCHAR(255),
	[rows] INT,
	reserved NVARCHAR(255),
	data NVARCHAR(255),
	index_size NVARCHAR(255),
	unused NVARCHAR(255)
);

DECLARE tnames_cursor CURSOR
FOR
   SELECT '[' + s.name + '].[' + t.name + ']'
   FROM sys.tables AS t
   JOIN sys.schemas AS s ON s.schema_id = t.schema_id
	ORDER BY s.name + '.' + t.name;
OPEN tnames_cursor;
DECLARE @tablename sysname;
FETCH NEXT FROM tnames_cursor INTO @tablename;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN
	  SELECT @tablename = RTRIM(@tablename)
      INSERT #T ( name , rows , reserved , data , index_size , unused )
          EXEC sp_spaceused @objname = @tablename
   END;
   FETCH NEXT FROM tnames_cursor INTO @tablename;
END;
CLOSE tnames_cursor;
DEALLOCATE tnames_cursor;

SELECT * FROM #T WHERE rows <> 0 ORDER BY CAST(REPLACE(reserved, ' KB', '') AS BIGINT);
DROP TABLE #T;