Tamany taules

Llistar el tamany de les taules d’una base de dades:

Métode 1

SELECT 
   sch.name as SchemaName,
   tab.name as TableName,   
   par.rows as RowCounts, 
   sum(alc.total_pages) * 8 / 1024 as EspaiTotalMB,
   sum(alc.used_pages) * 8 /1024 as EspaiUtilitzatMB,
   (sum(alc.total_pages) - sum(alc.used_pages)) * 8 as EspaiNoUtilitzat
FROM sys.tables tab 
INNER JOIN sys.indexes ind ON tab.object_id = ind.object_id 
INNER JOIN sys.partitions par ON ind.object_id = par.object_id and ind.index_id = par.index_id 
INNER JOIN sys.allocation_units alc ON par.partition_id = alc.container_id 
LEFT OUTER JOIN sys.schemas sch ON tab.schema_id = sch.schema_id 
GROUP BY 
   tab.name, 
   sch.name, 
   par.rows 
ORDER BY 5 desc;

Métode 2

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),      
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 
       unused VARCHAR(18))

EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,
       CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM #RowCountsAndSizes 
ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes

Visites: 380