Note: This query is the most detailed. It breaks out the Used space to Data space and Index space.

ReservedSpace = DataSpace + IndexSize + UnusedSpace

Note: This run needs to be ran on specific database on the server. 


USE MyDB
GO

IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL
	DROP TABLE #SpaceUsed

CREATE TABLE #SpaceUsed (
	 TableName sysname
	,NumRows BIGINT
	,ReservedSpace VARCHAR(50)
	,DataSpace VARCHAR(50)
	,IndexSize VARCHAR(50)
	,UnusedSpace VARCHAR(50)
	) 

DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO #SpaceUsed 
EXEC sp_msforeachtable @command1=@str

SELECT TableName, NumRows, 
(CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024) / 1024 as ReservedSpace_GB,
(CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024) / 1024 as DataSpace_GB,
(CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024) / 1024 as IndexSpace_GB,
(CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024) / 1024 as UnusedSpace_GB
FROM #SpaceUsed
ORDER BY ReservedSpace_GB desc

Sources:

https://www.mssqltips.com/sqlservertip/1177/determining-space-used-for-all-tables-in-a-sql-server-database/
Last modified: October 19, 2022

Author

Comments

Write a Reply or Comment