Top de Tablas del Sistema SQL Server más importantes
El uso de SQL Server como sistema de gestión de bases de datos relacional es una práctica común en muchas empresas y organizaciones. Uno de los aspectos fundamentales para administrar adecuadamente una base de datos en SQL Server es comprender las tablas del sistema. Estas tablas son esenciales para obtener información sobre la estructura de la base de datos, su configuración y su funcionamiento. En este blog, exploraremos las tablas del sistema más importantes en SQL Server, su propósito y cómo usarlas eficazmente.
¿Qué son las tablas del sistema en SQL Server?
Las tablas del sistema en SQL Server son un conjunto de tablas internas que el sistema utiliza para almacenar metadatos sobre la base de datos y sus componentes. Estos metadatos incluyen información sobre tablas, columnas, índices, permisos y otros elementos cruciales para el funcionamiento del sistema.
SQL Server organiza estas tablas en varios esquemas del sistema, principalmente en sys
y INFORMATION_SCHEMA
. Aunque los usuarios no deben modificar directamente estas tablas, es posible consultarlas para obtener información valiosa sobre la base de datos.
Principales tablas del sistema en el esquema sys
El esquema sys
contiene muchas tablas y vistas que proporcionan información detallada sobre la base de datos. A continuación, describimos algunas de las más importantes:
1. sys.objects
La tabla sys.objects
contiene una fila para cada objeto creado dentro de la base de datos, como tablas, vistas, procedimientos almacenados y funciones.
Ejemplo de consulta:
SELECT name, object_id, type_desc
FROM sys.objects
WHERE type = 'U'; -- U representa las tablas de usuario
Esta consulta retorna los nombres, identificadores de objeto y descripciones de tipo de todas las tablas de usuario en la base de datos.
2. sys.tables
La tabla sys.tables
es una vista que muestra una fila por cada tabla de usuario en la base de datos. Es una vista filtrada de sys.objects
.
Ejemplo de consulta:
SELECT name, create_date, modify_date
FROM sys.tables;
Esta consulta devuelve los nombres de las tablas junto con las fechas de creación y modificación.
3. sys.columns
La tabla sys.columns
contiene una fila por cada columna de cada objeto de tabla o vista en la base de datos.
Ejemplo de consulta:
SELECT table_name = t.name, column_name = c.name, c.column_id, c.data_type
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id;
Esta consulta lista todas las columnas y sus tipos de datos para cada tabla en la base de datos.
4. sys.indexes
La tabla sys.indexes
almacena información sobre los índices definidos en tablas y vistas.
Ejemplo de consulta:
SELECT name, index_id, type_desc, is_unique
FROM sys.indexes
WHERE object_id = OBJECT_ID('NombreDeLaTabla');
Reemplaza NombreDeLaTabla
con el nombre de la tabla específica para obtener información sobre sus índices.
5. sys.partitions
La tabla sys.partitions
proporciona una fila por cada partición de una tabla o índice en la base de datos.
Ejemplo de consulta:
SELECT object_name(p.object_id) AS table_name, i.name AS index_name, p.partition_number, p.rows
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE object_name(p.object_id) = 'NombreDeLaTabla';
Esta consulta muestra las particiones y el número de filas para una tabla específica.
Principales vistas del sistema en INFORMATION_SCHEMA
El esquema INFORMATION_SCHEMA
proporciona una forma estandarizada de obtener información sobre objetos de la base de datos. Aquí destacamos algunas vistas esenciales:
1. INFORMATION_SCHEMA.TABLES
Esta vista contiene una fila por cada tabla y vista en la base de datos.
Ejemplo de consulta:
SELECT table_name, table_type
FROM INFORMATION_SCHEMA.TABLES;
Esta consulta lista todas las tablas y vistas con sus tipos (base table o view).
2. INFORMATION_SCHEMA.COLUMNS
La vista INFORMATION_SCHEMA.COLUMNS
proporciona información sobre cada columna en cada tabla y vista de la base de datos.
Ejemplo de consulta:
SELECT table_name, column_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'NombreDeLaTabla';
Reemplaza NombreDeLaTabla
para obtener los detalles de las columnas de una tabla específica.
3. INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Esta vista muestra información sobre las columnas que son clave primaria o clave externa.
Ejemplo de consulta:
SELECT table_name, column_name, constraint_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
Esta consulta proporciona los nombres de tablas, columnas y restricciones relacionadas con claves primarias y externas.
4. INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
La vista INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
proporciona información sobre las columnas utilizadas en restricciones.
Ejemplo de consulta:
SELECT table_name, column_name, constraint_name
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE;
Esta consulta lista las columnas y las restricciones que las utilizan.
Uso de las tablas del sistema para optimización y administración
Las tablas del sistema no solo son útiles para obtener información sobre la estructura de la base de datos, sino que también son cruciales para la optimización y administración de la misma. Aquí hay algunos ejemplos de cómo puedes utilizarlas:
Identificación de índices no utilizados
Puedes usar sys.dm_db_index_usage_stats
junto con sys.indexes
para identificar índices que no se usan frecuentemente y que podrían eliminarse para mejorar el rendimiento.
Ejemplo de consulta:
SELECT i.name AS index_name, i.object_id, i.index_id, u.user_seeks, u.user_scans, u.user_lookups
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE i.object_id = OBJECT_ID('NombreDeLaTabla') AND u.user_seeks = 0 AND u.user_scans = 0 AND u.user_lookups = 0;
Monitorización de espacio de tabla
Puedes consultar sys.dm_db_partition_stats
para monitorizar el espacio utilizado por las tablas y los índices.
Ejemplo de consulta:
SELECT object_name(p.object_id) AS table_name, SUM(p.used_page_count) * 8 AS used_kb
FROM sys.dm_db_partition_stats p
GROUP BY object_name(p.object_id);
Esta consulta calcula el espacio utilizado en KB por cada tabla.
TOP de tablas del Sistema Importantes en SQL Server
1. sys.objects
Vista de sistema: sys.objects
Objetivo: Lista todos los objetos definidos en la base de datos, incluyendo tablas, vistas, procedimientos almacenados, funciones, etc.
2. sys.tables
Vista de sistema: sys.tables
Objetivo: Proporciona una lista de todas las tablas definidas en la base de datos.
3. sys.columns
Vista de sistema: sys.columns
Objetivo: Contiene una fila para cada columna de cada tabla o vista en la base de datos.
4. sys.indexes
Vista de sistema: sys.indexes
Objetivo: Lista todos los índices definidos en las tablas y vistas de la base de datos.
5. sys.partitions
Vista de sistema: sys.partitions
Objetivo: Proporciona información sobre las particiones de tablas e índices.
6. sys.schemas
Vista de sistema: sys.schemas
Objetivo: Lista todos los esquemas definidos en la base de datos.
7. sys.procedures
Vista de sistema: sys.procedures
Objetivo: Proporciona una lista de todos los procedimientos almacenados en la base de datos.
8. sys.views
Vista de sistema: sys.views
Objetivo: Contiene una fila para cada vista definida en la base de datos.
9. sys.triggers
Vista de sistema: sys.triggers
Objetivo: Lista todos los disparadores (triggers) definidos en las tablas y vistas.
10. sys.foreign_keys
Vista de sistema: sys.foreign_keys
Objetivo: Proporciona información sobre las claves foráneas definidas en la base de datos.
11. sys.key_constraints
Vista de sistema: sys.key_constraints
Objetivo: Lista todas las restricciones de clave primaria y única en las tablas de la base de datos.
12. sys.server_principals
Vista de sistema: sys.server_principals
Objetivo: Lista las conexiones definidas en el servidor, incluyendo logins y grupos.
13. sys.database_principals
Vista de sistema: sys.database_principals
Objetivo: Proporciona una lista de todos los usuarios y roles de la base de datos.
14. sys.sysconfigures
Vista de sistema: sys.sysconfigures
Objetivo: Contiene información sobre los parámetros de configuración del servidor.
15. sys.dm_exec_requests
Vista de sistema: sys.dm_exec_requests
Objetivo: Proporciona información sobre las solicitudes de ejecución que están en progreso en el servidor SQL.
16. sys.dm_exec_sessions
Vista de sistema: sys.dm_exec_sessions
Objetivo: Lista todas las sesiones actuales que están conectadas al servidor SQL.
17. sys.dm_tran_locks
Vista de sistema: sys.dm_tran_locks
Objetivo: Proporciona información sobre los bloqueos de transacciones en el servidor SQL.
18. sys.dm_os_wait_stats
Vista de sistema: sys.dm_os_wait_stats
Objetivo: Proporciona información sobre los tipos de esperas que se producen en el servidor SQL.
Estas tablas y vistas del sistema son cruciales para la administración y el monitoreo de bases de datos en SQL Server, proporcionando información detallada sobre la estructura de la base de datos, la configuración del servidor y las actividades de los usuarios.
Conclusión
Comprender las tablas del sistema en SQL Server es fundamental para cualquier administrador de bases de datos. Estas tablas proporcionan una visión detallada de la estructura, configuración y rendimiento de la base de datos, permitiendo una administración y optimización eficientes. Utilizando las tablas del sistema sys
y las vistas de INFORMATION_SCHEMA
, puedes obtener y analizar información crítica que te ayudará a mantener y mejorar tus bases de datos SQL Server.
Generando Script de creación de Usuarios en SQL Server
Script para saber el histórico de queries ejecutados SQL
SQL: ¿Configurar los Archivos LDF y MDF en Unidades Distintas es Importante?
UPDATE JOIN en SQL para Actualizar Tablas Relacionadas
Monitoreo y Mantenimiento SQL: Mantén Tu Base de Datos Saludable