Script–Obtener información física de tabla e índices – UPDATE

Este script es muy útil para obtener información de donde esta almacenada la tabla o índice, que tipo de estructura es, cuantos registros tiene y su tamaño

Inicialmente la información se limitaba a lo descrito, pero en una ocasión me topé con una base de datos con tablas particionadas, así que tuve que mejorar el script incluyendo las particiones. Primero voy a mostrar el script y luego detallaremos la información que se muestra. Aquí va:

SELECT
  DB_ID() As ID_BD,
  DB_NAME() AS NombreBD,
  SCHEMA_NAME(schema_id) AS Esquema,
  t.name  AS Tabla, 
  CASE 
    WHEN MAX(p.partition_number) OVER(Partition by schema_id, t.name) = 1 THEN 'N'
    ELSE 'S'
  END 'TablaParticionada',
  f.name AS GrupoArchivo, 
  i.name AS Indice,  
  --i.type AS TipoEstructura,
  i.type_desc AS TipoEstructura,
  au.type_desc AS TipoParticion,
  p.rows AS NumRegistros,  
  (au.total_pages*8.) AS TamañoKB
FROM sys.tables t
 INNER JOIN sys.indexes i 
  ON t.object_id = i.OBJECT_ID
 LEFT OUTER JOIN sys.partitions p 
  ON t.object_id=p.object_id 
  AND i.index_id=p.index_id
 LEFT OUTER JOIN sys.allocation_units au 
  ON au.container_id = p.hobt_id
 LEFT OUTER JOIN sys.filegroups f
  ON f.data_space_id = au.data_space_id
GO

SELECT

DB_ID() As ID_BD,

DB_NAME() AS NombreBD,

SCHEMA_NAME(schema_id) AS Esquema,

t.name AS Tabla,

CASE

WHEN MAX(p.partition_number) OVER(Partition by schema_id, t.name) = 1 THEN 'N'

ELSE 'S'

END 'TablaParticionada',

f.name AS GrupoArchivo,

i.name AS Indice,

--i.type AS TipoEstructura,

i.type_desc AS TipoEstructura,

au.type_desc AS TipoParticion,

p.rows AS NumRegistros,

(au.total_pages*8.) AS TamañoKB

FROM sys.tables t

INNER JOIN sys.indexes i

ON t.object_id = i.OBJECT_ID

LEFT OUTER JOIN sys.partitions p

ON t.object_id=p.object_id

AND i.index_id=p.index_id

LEFT OUTER JOIN sys.allocation_units au

ON au.container_id = p.hobt_id

LEFT OUTER JOIN sys.filegroups f

ON f.data_space_id = au.data_space_id

Lo primero que vamos a describir son las vistas del sistema que estamos utilizando. No vamos a entrar en mucho detalle por lo que incluimos un vínculo a los libros en línea por si desean mayor información:

sys.tables: esta vista es conocida, lista las tablas de la base de datos, pero solo las tablas de usuarios. No incluye las del sistema
sys,indexes: esta vista nos brinda información sobre el tipo de estructura física. Con esto averiguamos si la tabla es un montón o un índice agrupado (clúster) y también lista los índices no agrupados (no clúster). Si no estas familiarizado con estos término, te recomiendo que leas este artículo: Conceptos básicos de los índices
sys.partitions: Cuando creamos una tabla, esta se crea en una partición. Una tabla simple tiene una partición. Pero las tablas particionadas tienen, bueno, varias particiones. Con esta vista obtenemos información de la partición. Si qiueres conocer más sobre cómo SQL Server organiza las tablas e índices, te recomiendo que leas este artículo: Organización de tablas e índices
sys.allocation_units: Esta vista especifica cómo se están asignado la ubicación de datos. Dependiendo de los tipos de datos usados en los campos, se asignan ubicaciones de datos. Estas pueden ser de tres tipos:
- IN_ROW_DATA: Es la más común. Incluye todos los tipos de datos excepto los LOB
- LOB_DATA: Contiene tipos de dato texto o imagen de tipo LOB (Large Object – Objeto grande)
- ROW_OVERFLOW_DATA: Son datos de tipo texto o imagen que superan el límite de tamaño máximo de 8060 bytes (tamaño de una página)
sys.filegroups: Esta vista nos indica el nombre del grupo de archivos o esquema de partición (para tablas particionadas) a la que pertenece la tabla o índice. Si no estas familiarizado con estos término, te recomiendo que leas este artículo: Archivos de Bases de Datos

NOTA: Aunque el script muestra los índices de tipo XML y espaciales, en este artículo no vamos a ahondar sobre esos tipos

Vamos a mostrar el resultado parcial de la consulta para explicar qué es lo que obtenemos. Antes indicamos que la tabla Person.PersonPhone ha sido movida a un grupo de archivos y la tabla Sales.SalesOrdeDetail ha sido particionada en tres partes, lo cual vamos a notar a continuación:

Hay algunos campos que se explican por si solos, pero vamos a mencionar algunos en particular:

TablaParticionada: Es un flag SI o NO que indica si la tabla está particionada. Se observa que la tabla SalesOrderDetail esta dividida en tres grupos de archivos
TipoEstructura: Indica si el índice es agrupado (clúster) o no agrupado (no clúster). Si este campo mostrara NULL, la estructura sería de tipo montón o HEAP
NumRegistros: Es el número de registro de la estructura. Es una forma rápida y práctica de obtener esa información sin necesidad de efectuar un COUNT() a la tabla
TamañoKB: Es el tamaño total de la tabla

Podemos añadir al script una cláusula ORDER BY para obtener primero las tablas con más registros o de mayor tamaño

Comparte Este artículo:

Deja una respuesta Cancelar la respuesta