Quite a few times I've worked with groups that couldn't tell you what tables in their database were the largest, or what component (data, indexes, or even unused space) was using the most space. I've pulled around a very simple script for some time that has served me well.
SET NOCOUNT ON
CREATE TABLE #results(Name sysname,Rows char(11),reserved varchar(18),Data varchar(18),index_size varchar(18),Unused varchar(18))
DECLARE @obj_id int
SET @obj_id = -2147483648
DECLARE @obj_name sysname
SELECT TOP 1 @obj_id = id, @obj_name = name FROM sysobjects WHERE ( xtype = 'U') AND id > @obj_id
WHILE (@@ROWCOUNT = 1)
BEGIN
INSERT INTO #results
EXEC sp_spaceused @obj_name
SELECT TOP 1 @obj_id = id, @obj_name = name FROM
sysobjects WHERE ( xtype = 'U') AND id > @obj_id
END
-- Prune off the "KB"
UPDATE
#results
SET
reserved = LEFT(reserved,LEN(reserved)-3)
,data = LEFT(data,LEN(data)-3)
,index_size = LEFT(index_size,LEN(index_size)-3)
,Unused = LEFT(Unused,LEN(Unused)-3)
-- Convert to
ALTER TABLE #results ALTER COLUMN reserved bigint
ALTER TABLE #results ALTER COLUMN Data bigint
ALTER TABLE #results ALTER COLUMN index_size bigint
ALTER TABLE #results ALTER COLUMN Unused bigint
SELECT
Name
,reserved AS [Size On Disk (KB)]
,data AS [Data Size (KB)]
,index_size AS [Index Size (KB)]
,Unused AS [Unused (KB)]
,Rows FROM #results
ORDER BY
reserved DESC
DROP TABLE #results
Email Address:
(your email address is not displayed)
Website:
Comment *:
Dennis Forbes is a Toronto-based software architect. While focused primarily on the .NET and SQL Server worlds, Dennis frequently ventures outside of this comfort zone into game development and image processing. He has been published in several industry magazines, has been quoted in the Wall Street Journal and has been interviewed by NPR.He is a vice president and lead software architect at an innovative New York City hedge fund back-office services firm.
Dennis has been working on solutions for the financial, telecommunications, and power generation markets for over 15 years.