PREGUNTA: Como puedo saber cual es el espacio utilizado por todas mis tablas en una base de datos
RESPUESTA:
NOTA: En todo los artículos del blog, haciendo clic en la imagen obtendrás información ampliada sobre los temas tratados.
Una respuesta inmediata sería recomendar el uso del procedimiento almacenado del sistema sp_spaceused (). Pero, si usamos este procedimiento almacenado para obtener información de tablas, tenemos que usarlo para una tabla a la vez. He encontrado artículos al respecto y sugieren crear un programa que liste todas las tablas de la base de datos para luego hacer un rulo (loop) por cada tabla, pasando el nombre del objeto al procedimiento almacenado sp_spaceused en un query dinámico, almacenar cada pasada del rulo en una tabla temporal y por ultimo listar la tabla resultante.
Muy complicado!
Para saber cuanto registros tienen tus tabla e información detallada de el espacio que ocupan, solo tienes que ejecutar el simple query que presento a continuación.
NOTAS:
- El ejemplo mostrado aplica para SQL Server 2005 o mayor.
- Estoy usando CTE (Common Table Expresión
) que sí genera un conjunto de datos temporal, pero en el mismo Batch, de modo que no hay que definir un objeto tabla temporal.
- Si tienes tablas con campos e índices XML y requieres información muy precisa, alli sí recomiendo el uso de sp_spaceused, ya que en ese procedimiento almacenado se hacen algunos cálculos especiales requeridos para obtener de forma precisa el espacio utilizado cuando se usa XML.
Aqui el query: (hacer clic en la imagen para obtener el script->)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
--Opcional para actualizar el conteo de filas y uso de páginas --DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS --CTE con información por páginas. Al hacer Join con sys.tables solo aparecerán las tablas de usuario WITH EspacioTablas_CTE (Esquema,Objeto,RowCnt,ResPag,DatPag,UsdPag) AS ( SELECT t.schema_id, --Esquema p.object_id, --Objeto SUM(CASE WHEN p.index_id < 2 THEN p.row_count ELSE 0 END), --RowCnt SUM(p.reserved_page_count), --ResPag SUM(CASE WHEN p.index_id<2 THEN p.in_row_data_page_count+p.lob_used_page_count+p.row_overflow_used_page_count ELSE p.lob_used_page_count+p.row_overflow_used_page_count END), --DatPag SUM(p.used_page_count) --UsdPag FROM sys.dm_db_partition_stats p JOIN sys.tables t ON p.object_id=t.object_id GROUP BY t.schema_id, p.object_id) --Query final con información en KB, Ordenado por la tabla con mayor cantidad de registro SELECT SCHEMA_NAME(Esquema) AS Esquema, OBJECT_NAME(Objeto) AS Tabla, RowCnt AS [Num Registros], (ResPag * 8) AS [KB Reservados], (DatPag * 8) AS [KB Datos], CASE WHEN UsdPag > DatPag THEN (UsdPag - DatPag) * 8 ELSE 0 END [KB Indices], CASE WHEN ResPag > UsdPag THEN ResPag - UsdPag ELSE 0 END [KB Sin Usar] FROM EspacioTablas_CTE ORDER BY [Num Registros] DESC GO |
Mug buen articulo, gracias
Gracias Enrique, espero que le puedas sacar provecho de inmediato.
Excelente consulta, ya la probé y tome acciones sobre mi BD, gracias por la publicación.