Table Space Used in DB

,

So I was looking for a script that I wrote to sort tables by size years ago, it was 3am and I remember how fiddly it was in that old script where I had to change the script for the sort that I wanted to do. Because I couldn't find this script right away, I decided to write it from scratch. I also work with a lot of multi-schema databases and without cloning the sp_spaceused for schema output I decided to also add that info to the output. I tried making this really simple, I've tested this in 2008, 2012 & 2014

And I'm so glad I did, I decided to try out a sort concept I thought of that works quite well and all you need to change is just 1 character. 

/*
 
--  _______         _      _            _____  _                 
-- |__   __|       | |    | |          / ____|(_)                
--    | |     __ _ | |__  | |  ___    | (___   _  ____  ___  ___ 
--    | |    / _` || '_ \ | | / _ \    \___ \ | ||_  / / _ \/ __|
--    | |   | (_| || |_) || ||  __/    ____) || | / / |  __/\__ \
--    |_|    \__,_||_.__/ |_| \___|   |_____/ |_|/___| \___||___/
 
-- Author  : Luis.Chiriff@gmail.com
-- Date    : 2018-07-28 03:15:19
-- Purpose : Lists all tables in the database and allows you to sort via int
             
*/
 

-- Declaration

Declare @Results table (Name varchar(200),DRows bigint, DResv varchar(150), DData varchar(150), DIdx varchar(150), DUnU varchar(150), FixName int, RReserved bigint, RData bigint, RIndex bigint, RUnUsed bigint, IDx int identity(1,1))
Declare @List table (Name varchar(200), IDx int identity(1,1))
Declare @Mn int, @Mx int, @Name varchar(200), @SortType int

-- Config

SET @SortType = 5
--(1 = Name ASC, 2 = Name Desc, 3 = DRows Desc, 4 = RData Desc, 5 = RReserved Desc, 6 = RIndex Desc)

-- Gather

INSERT INTO @List
	SELECT '['+Schema_Name(uid)+'].['+Name+']' FROM SYSOBJECTS WHERE XTYPE = 'U' ORDER BY NAME ASC

SET @Mx = @@ROWCOUNT
SET @Mn = 1

WHILE(@Mn <= @Mx)
	BEGIN
	
	SELECT @Name = Name FROM @List WHERE IDx = @Mn
		
	INSERT INTO @Results (Name,DRows,DResv,DData,DIdx,DUnU)
		EXEC SP_SPACEUSED @Name
		
	UPDATE @Results SET Name = @Name, FixName = 1 WHERE FixName IS NULL
		
	SET @Mn += 1
	END

UPDATE @Results SET RData = CAST(REPLACE(DData,' KB','') as bigint)*1024, RReserved = CAST(REPLACE(DResv,' KB','') as bigint)*1024, RIndex = CAST(REPLACE(DIDx,' KB','') as bigint)*1024,
RUnUsed = CAST(REPLACE(DUnU,' KB','') as bigint)*1024

-- Return Data

SELECT Name, DRows, RReserved, RData, RIndex, RUnUsed, CAST(RReserved/1024/1024.0 as Decimal(9,2)) as ReservedInMB, (CASE @SortType WHEN 1 THEN 0 WHEN 2 THEN IDX WHEN 3 THEN DRows WHEN 4 THEN RData WHEN 5 THEN RReserved WHEN 6 THEN RIndex ELSE 0 END) as SortOrder
FROM @Results ORDER BY 8 desc, 1 asc

Rate

4 (5)

Share

Share

Rate

4 (5)