Genera Reporte de Tablas de SQL Server

Tener un inventario de objetos que administramos siempre será de gran ayuda para saber qué tenemos y cómo cambian en el tiempo nuestros recursos. Generar un Reporte de Tablas de SQL Server también nos ayudará en este propósito. Si no tenemos clara la idea de todo el contenido de nuestras bases de datos, dificilmente podremos plantear un análisis detallado de las situaciones que enfrentamos.

En este caso particular en el que quiero hablarte de las tablas de SQL Server, tener un inventario es tan fácil como utilizar la vista de sistema sys.tables y recopilar los datos más importantes.

Sin embargo, necesitamos más que una lista para generar un análisis más detallado.

LA CANTIDAD DE FILAS DETERMINA EL TAMAÑO DE LA TABLA

¡Falso! Mientras más registros tenga una tabla, no quiere decir que vaya a tener un mayor tamaño. Una concepción totalmente errada es justamente pensar que el tamaño de una tabla depende de cuántas filas tiene dentro de ella.

Si alguna vez has pensado que tus consultas están lentas porque tus tablas tienen muchos datos, es posible que no hayas realizado un análisis completo del espacio físico utilizado.

NECESITAS UN ANÁLISIS DEL ALMACENAMIENTO

Una lista de tablas de SQL Server no te sirve más que para saber cuántas de ellas tienes en una base de datos. El punto clave acá es poder saber cuánto de almacenamiento tiene cada una.

Para generar un reporte de tablas de SQL Server te propongo utilizar más tablas y recopilar más información. Podemos empezar con la adición de las siguientes:

• sys.tables
• sys.indexes
• sys.partitions
• sys.allocation_units
• sys.schemas

Y unirlas para la obtención de un Reporte más completo que considere el tamaño en filas y el tamaño en espacio físico utilizado para cada tabla.

REPORTE DE TABLAS DE SQL SERVER

Puede ser fácilmente generado con la ejecución de un procedimiento almacenado denominado sp_ReportUserDbTables cuyo resultado puedes observarlo en la imagen siguiente.

No tiene mayor complicación que la unión de las vistas de sistema mencionadas en el punto anterior y puedes obtener el código fuente aquí en nuestro repositorio de Github o usar el código a continuación.

CREATE PROCEDURE dbo.sp_ReportUserDbTables
AS
BEGIN 

	WITH rep AS(
	SELECT
		SchemaName = s.Name, 
		ObjectName = t.NAME,
		t.create_date AS CreateDate, 
		t.modify_date AS ModifyDate,
		p.rows AS RowCounts,
		SUM(a.total_pages) * 8 AS TotalSpaceKB, 
		SUM(a.used_pages) * 8 AS UsedSpaceKB, 
		(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
	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.is_ms_shipped = 0
		AND i.OBJECT_ID > 255 
	GROUP BY 
		s.Name, t.NAME, t.create_date, t.modify_date, p.Rows
	)

	SELECT 	REPORT = 'DATABASE TABLES',
		ServerName = @@SERVERNAME,
		DatabaseName = DB_NAME(),
		*
	FROM rep
	ORDER BY 4, 5
END

A partir de este reporte, puedes analizar el crecimiento de tus tablas, la cantidad de espacio físico que requieren en el tiempo o incluso la cantidad de espacio libre que dejas con ellas.

Este es un primer paso en el análisis del uso de tablas de bases de datos de usuarios; esto quiere decir que el comportamiento con bases de datos de sistema puede ser diferente particulamente cuando queremos analizar qué pasa con la base tempdb.

No te olvides lo importante que es tener un control y seguimiento de cómo cambia el almacenamiento requerido por las bases de datos. En este caso, el revisar periódicamente el comportamiento de las tablas te ayudará en este propósito. Posterior puedes hacer un análisis del uso de los índices para apoyar este trabajo.