Dimensioni delle tabelle di un Database in SQL Server

Nel post di oggi vedremo come ottenere le Dimensioni delle tabelle di un Database in SQL Server tramite SSMS (SQL Server Management Studio).
Tramite la stored procedure sp_spaceused, messa a disposizione da SQL Server, è possibile visualizzare tutte le informazioni relative ad una tabella, una vista indicizzata o una coda di Service Broker come lo spazio riservato e utilizzato all’interno del database o del disco.

Dimensioni delle tabelle di un Database in SQL Server

sp_spaceused

La stored procedure (sp_spaceused) permette di visualizzare le informazioni richiamandola per ogni singola tabella di cui, però, dobbiamo già conoscere il nome da passargli come parametro.
La si può richiamare in questo modo

exex sp_spaceused('table_name')

Query

Se però abbiamo la necessità di visualizzare la dimensione di tutte le tabelle di uno specifico database, e magari ordinare l’elenco in base alle dimensioni delle tabelle, dobbiamo utilizzare una query più complessa.
Questa è la query che ci permette di visualizzare le dimensioni di tutte le tabelle di un DataBase

	--lelezapp.it
	
	--SET database context
	USE [DataBaseName]
	
	--Declare Variable
	DECLARE @sql VARCHAR(128)
	DECLARE @name VARCHAR(128)

	--Create temporary table #Tables
	CREATE TABLE #Tables(Name VARCHAR(128))
	
	--Insert all TABLE_NAME inside the temporary table #Tables
	SET @sql = 'INSERT #Tables SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
	EXEC(@sql)
	
	--Create temporary table @SpaceUsed 
	CREATE TABLE #SpaceUsed 
		(Name VARCHAR(128), Rows VARCHAR(11), Reserved VARCHAR(18), Data VARCHAR(18), IndexSize VARCHAR(18), Unused VARCHAR(18))
	
	--Inserts the value in the #SpaceUsed table
	SET @name = ''
	WHILE exists (SELECT * FROM #Tables WHERE Name > @name)
	BEGIN
		SELECT @name = min(Name) FROM #Tables WHERE Name > @name
		SET @sql = 'EXEC ..sp_executesql N''insert #SpaceUsed exec sp_spaceused [' + @name + ']'''
		EXEC(@sql)
	END
	
	--Select and convert all values
	SELECT 
		Name,
		CAST(Rows AS DECIMAL) as Rows,
		CAST(left(Reserved, LEN(Reserved) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 AS Reserved,
		CAST(left(Data, LEN(Data) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 AS Data,
		CAST(left(IndexSize, LEN(IndexSize) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 as IndexSize,
		CAST(left(Unused, LEN(Unused) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 as Unused
	FROM #SpaceUsed
	ORDER BY Data DESC, Reserved DESC
	
	--Delete temporary tables
	DROP TABLE #tables
	DROP TABLE #SpaceUsed

Il codice è abbastanza commentato in modo da capire i passaggi che vengono effettuati prima di arrivare ad avere l’elenco delle tabelle con le rispettive dimensioni.

Descrizione della query

Per prima cosa indichiamo il database su cui verrà eseguita la query

	--SET database context
	USE [DataBaseName]

Dichiariamo le variabili che verranno utilizzare

	
	--Declare Variable
	DECLARE @sql VARCHAR(128)
	DECLARE @name VARCHAR(128)

Creiamo la tabella temporanea #Tables e la popoliamo con tutti i nomi delle tabelle presenti nel database (recuperati dalla tabella INFORMATION_SCHEMA.TABLES)

 
	--Create temporary table #Tables
	CREATE TABLE #Tables(Name VARCHAR(128))
	
	--Insert all TABLE_NAME inside the temporary table #Tables
	SET @sql = 'INSERT #Tables SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
	EXEC(@sql)

Creiamo la tabella temporanea #SpaceUsed e, ciclando la tabella temporanea #Tables, la popoliamo con i valori di ogni singola tabella recuperati tramite la stored procedure sp_spaceused

	--Create temporary table @SpaceUsed 
	CREATE TABLE #SpaceUsed 
		(Name VARCHAR(128), Rows VARCHAR(11), Reserved VARCHAR(18), Data VARCHAR(18), IndexSize VARCHAR(18), Unused VARCHAR(18))
	
	--Inserts the value in the #SpaceUsed table
	SET @name = ''
	WHILE exists (SELECT * FROM #Tables WHERE Name > @name)
	BEGIN
		SELECT @name = min(Name) FROM #Tables WHERE Name > @name
		SET @sql = 'EXEC ..sp_executesql N''insert #SpaceUsed exec sp_spaceused [' + @name + ']'''
		EXEC(@sql)
	END

Ora non ci resta che effettuare una SELECT sulla tabella temporanea #SpaceUsed in modo da visualizzare tutti i dati delle tabelle del database.
Per una migliore interpretazione dei dati convertiamo tutti i valori in GByte e li ordiniamo dalla più grande in modo decrescente

	
	--Select and convert all values
	SELECT 
		Name,
		CAST(Rows AS DECIMAL) as Rows,
		CAST(left(Reserved, LEN(Reserved) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 AS Reserved,
		CAST(left(Data, LEN(Data) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 AS Data,
		CAST(left(IndexSize, LEN(IndexSize) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 as IndexSize,
		CAST(left(Unused, LEN(Unused) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 as Unused
	FROM #SpaceUsed
	ORDER BY Data DESC, Reserved DESC

Al termine della select eliminiamo le tabelle temporanee

	
	--Delete temporary tables
	DROP TABLE #tables
	DROP TABLE #SpaceUsed