How to: Check which table is the biggest, in SQL Server
- by AngelEyes
The company I work with had it's DB double its size lately, so I needed to find out which tables were the biggest.
I found this on the web, and decided it's worth remembering!
Taken from http://www.sqlteam.com/article/finding-the-biggest-tables-in-a-database, the code is from http://www.sqlteam.com/downloads/BigTables.sql
/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* [email protected]
* v1.1
*
**************************************************************************************/
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
