In that prior example I used a cursor technique that might be alien to some - rather than using a DECLARE CURSOR, I iterated through the records by id.
I came across this at one shop that I did some consulting. At first I approached it with skepticism - It seemed like an unnecessarily quirkly way of doing a cursor - but after several benchmarks I was sold. If the iterating id is indexed (which it often is), the performance advantage will vary between marginal to significant.
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
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.