Table size and heaviest table

-- sp_spaceused pay_detl

DECLARE @id INT
DECLARE @type character(2)
DECLARE @pages INT
DECLARE @dbname SYSNAME
DECLARE @dbsize dec(15, 0)
DECLARE @bytesperpage dec(15, 0)
DECLARE @pagesperMB dec(15, 0)

CREATE TABLE #spt_space
(
objid INT NULL,ROWS INT NULL,reserved dec(15) NULL,
DATA dec(15) NULL,indexp dec(15) NULL,unused dec(15) NULL
)

SET NOCOUNT ON

-- Create a cursor to loop through the user tables
DECLARE c_tables CURSOR
FOR
SELECT id FROM sysobjects WHERE xtype = 'U'
OPEN c_tables

FETCH NEXT FROM c_tables
INTO @id

WHILE @@fetch_status = 0
BEGIN
/* Code from sp_spaceused */
INSERT INTO #spt_space
(
objid, reserved
)
SELECT objid = @id, SUM(reserved) FROM sysindexes
WHERE indid IN (0, 1, 255) AND id = @id

SELECT @pages = SUM(dpages) FROM sysindexes
WHERE indid < 2 AND id = @id

SELECT @pages = @pages + ISNULL(SUM(USED), 0)
FROM sysindexes WHERE indid = 255 AND id = @id

UPDATE #spt_space SET DATA = @pages WHERE objid = @id


/* index: sum(used) where indid in (0, 1, 255) - data */
UPDATE #spt_space
SET indexp = (
SELECT SUM(USED) FROM sysindexes WHERE indid IN (0, 1, 255) AND id = @id
) - DATA
WHERE objid = @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
UPDATE #spt_space
SET unused = reserved
-(
SELECT SUM(USED) FROM sysindexes WHERE indid IN (0, 1, 255)
AND id = @id
)
WHERE objid = @id

UPDATE #spt_space SET ROWS = i.rows
FROM sysindexes i WHERE i.indid < 2
AND i.id = @id AND objid = @id

FETCH NEXT FROM c_tables
INTO @id
END

SELECT TOP 25 Table_Name = (SELECT LEFT(NAME, 25) FROM sysobjects WHERE id = objid ),
ROWS = CONVERT(CHAR(11), ROWS),reserved_KB = LTRIM(STR(reserved * d.low / 1024., 15, 0) + ' ' + 'KB'),
data_KB = LTRIM(STR(DATA * d.low / 1024., 15, 0) + ' ' + 'KB'),
index_size_KB = LTRIM(STR(indexp * d.low / 1024., 15, 0) + ' ' + 'KB'),
unused_KB = LTRIM(STR(unused * d.low / 1024., 15, 0) + ' ' + 'KB')
FROM #spt_space, MASTER.dbo.spt_values d
WHERE d.number = 1 AND d.type = 'E'
ORDER BY reserved DESC

DROP TABLE #spt_space
CLOSE c_tables
DEALLOCATE c_tables