Consultoria DBA SQL Server

Saiba listar as maiores tabelas no SQL Server no seu banco de dados.

Recentemente precisei listar as maiores tabelas no SQL Server para fins de expurgo, utilizando algumas views do SQL Server consegui obter o resultado demonstrado na figura abaixo.

Script usado:

SELECT 
 t.NAME AS TableName,
 s.Name AS SchemaName,
 p.rows AS QuantidadeDeLinhas,
 SUM(a.total_pages) * 8 / 1024 AS EspacoTotalEmMB, 
 SUM(a.used_pages) * 8 / 1024 AS EspacoUtilEmMB, 
 (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUtilizadoEmKB
FROM 
 sys.tables t
INNER JOIN 
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
 sys.schemas s ON t.schema_id = s.schema_id
WHERE 
 t.NAME NOT LIKE 'dt%' 
 AND t.is_ms_shipped = 0
 AND i.OBJECT_ID > 255 
GROUP BY 
 t.Name, s.Name, p.Rows
ORDER BY 
 4 DESC