Pages

Wednesday, April 21, 2010

Space used by SQL tables

This SQL might be useful to you.  It will show you which tables in your SQL 2005+ database are taking up the most space.  Parts of the code were taken from the sp_dbsize system stored procedure.

DECLARE @TotalReserved AS INT
-- Get the database size...
SELECT @TotalReserved = sum(convert(dec(15),reserved)) * 8192 / 1024
FROM sysindexes
WHERE indid in (0, 1, 255)

SELECT *,
CASE WHEN Rows > 0 THEN Reserved * 1024 / Rows ELSE -1 END as 'AvgBytesPerRow',
Reserved * 100 / @TotalReserved as 'Percent',
replicate( 'X', Reserved * 100 / @TotalReserved ) 'Reserved Space Graph'
FROM (
select t.Table_Name,
(
SELECT CAST(CAST(SUM(Reserved) AS FLOAT) * 8192 / 1024 AS NUMERIC )
FROM sysindexes
WHERE id = OBJECT_ID( t.Table_Name ) and indid in (0, 1, 255)
) as 'Reserved',
(
SELECT i.rows
FROM sysindexes i
WHERE i.indid < 2
AND i.id = OBJECT_ID( t.Table_Name )
) as 'Rows'
FROM information_schema.tables t
) a
ORDER BY Reserved DESC