Object Sizes in SQL Server

Quite a few times I’ve worked with groups that couldn’t tell youwhat 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 hasserved me well.

SET NOCOUNT ON

CREATE TABLE #results(Name sysname,Rows char(11),reservedvarchar(18),Data varchar(18),index_size varchar(18),Unusedvarchar(18))

DECLARE @obj_id int
SET @obj_id = -2147483648

DECLARE @obj_name sysname

SELECT TOP 1 @obj_id = id, @obj_name = name FROM sysobjectsWHERE ( 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 FROMsysobjects 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