Subscribe to this blog
Briefcase
Print
This script only works on SQL Server 2000, not SQL Server 2005. Let me rephrase that. It'll work, but it may not be accurate because of some changes in physical structure in the new version. The script is designed to be placed in the master database though if you have a work database, it could work there, too. If in a work database, change it so the stored procedure doesn't begin with sp_ as SQL Server will look in the master database first, even if the three part naming convention is used.
USE master
GO
IF EXISTS (SELECT id FROM sysobjects WHERE name = 'sp_ReportTableSizes' AND xtype = 'P')
DROP PROC sp_ReportTableSizes
GO
CREATE PROC sp_ReportTableSizes
@ConversionType char(1) = 'K'
AS
BEGIN
DECLARE @conversion float
IF @ConversionType = 'B' SET @conversion = 1.0
ELSE IF @ConversionType = 'M' SET @conversion = (1024.0 * 1024.0)
ELSE IF @ConversionType = 'G' SET @conversion = (1024.0 * 1024.0 * 1024.0)
ELSE SET @conversion = 1024.0
SELECT su.tablename, (su.tablesize * spt.low / @conversion) totalsize
FROM master..spt_values spt,
(SELECT so.name tablename, SUM(si.reserved) tablesize
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE si.indid IN (0, 1, 255)
AND so.xtype = 'U'
GROUP BY so.name) su
WHERE spt.number = 1
AND spt.type = 'E'
ORDER BY su.tablesize DESC, su.tablename ASC
END
GO
No comments.