Consulta el tamaño de las tablas en SQL Server

Cuando estamos analizando por qué una base de datos ocupa mucho espacio, una forma de investigarlo es visualizar el tamaño que ocupa cada tabla.

Esto puede obtenerse fácilmente con una consulta como esta:

USE [NombreBD] -- reemplazar nombre base de datos
GO
SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
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
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
GO 


Tengamos en cuenta que las bases de datos de SQL Server Express solo pueden ocupar hasta 10 GB de espacio por lo tanto es importante monitorear este dato.

SQL Table Size Query

Author: Marcelo Ruiz

Marcelo has been working as a software developer for more than 15 years. He has participated in projects for companies in USA, Mexico, Argentina, Europe and Africa. He is skilled with Microsoft technologies such as ASP.NET, MVC, C#, WCF and SQL Server, among others.

Leave a Reply

Your email address will not be published.