Introducción
En este nuevo artículo, hablaremos acerca de las vistas/tablas/funciones de sistema y cómo resolver problemas comunes usándolas.
Las vistas de sistema son vistas que contienen información interna acerca de la Base de Datos.
La base de datos master, por ejemplo, contiene información acerca de SQL Server en sí mismo, mientras que la base de datos msdb contiene información acerca del agente SQL Server y cada base de datos sus propias vistas/tablas.
En este artículo mostraremos cómo obtener la lista de tablas, vistas, procedimientos almacenados, cómo obtener una lista de tablas de todas las bases de datos, cómo encontrar una tabla en múltiples bases de datos, cómo obtener la lista de usuarios, inicios de sesión, inicios de sesión mapeados, cómo detectar una fragmentación en una tabla y más.
Comencemos con algunas preguntas frecuentes acerca de bases de datos que pueden ser resueltas con las vistas/tablas/funciones del sistema:
¿Cómo puedo obtener una lista de tablas en una base de datos?
The following queries can provide you with that information about the database tables:
Opción 1
|
1 |
SELECT * FROM [INFORMATION_SCHEMA].[TABLES] |
Opción 2
|
1 2 |
SELECT * FROM sysobjects WHERE xtype = 'U' |
Usted puede usar vistas de esquema (INFORMATION_CHEMA.TABLES) o las vistas sysobjects directamente. De acuerdo a los libros, es mejor usar las vistas INFORMATION_SCHEMA porque la estructura interna no cambiará en el futuro. La vista sysobjects contiene información útil acerca de los diferentes objetos de la base de datos.
Xtype es el tipo de objeto y los posibles valores son:
SQ = Cola de servicio
TA = Desencadenador DML del ensamblado (CLR)
TF = Función de tabla
TR = desencadenador DML SQL
TT = Tipo de tabla
U = Tabla de usuario
UQ = Restricción UNIQUE (de tipo K)
V = Vista
X = Procedimiento almacenado extendido
Para más información acerca de la vista sysobjects, usted puede ir a las referencias.
¿Cómo puedo obtener la lista de vistas en una base de datos?
La solución es similar a la lista de tablas. Hay dos opciones para resolver este problema:
Opción 1
|
1 2 |
SELECT * FROM [INFORMATION_SCHEMA].[VIEWS] |
Opción 2
|
1 2 3 |
SELECT * FROM sysobjects WHERE xtype = 'V' |
[INFORMATION_SCHEMA].[VIEWS] contiene información acerca de las vistas, o usted puede encontrar la información en la vista sysobjects.
¿Cómo puedo obtener la lista de procedimientos en una base de datos?
Hay 2 opciones para resolver este problema:
|
1 2 |
select * from [INFORMATION_SCHEMA].[ROUTINES] where routine_type='PROCEDURE' |
|
1 2 3 |
SELECT * FROM sysobjects WHERE xtype = 'P' |
[INFORMATION_SCHEMA].[ROUTINES] contiene información acerca de procedimientos almacenados y funciones. Sysobjects es una segunda opción para obtener esta información.
¿Cómo puedo obtener la fecha de creación de una tabla específica?
El siguiente código T-SQL muestra la fecha de creación de la tabla ProductDocument:
|
1 2 3 4 5 |
SELECT crdate FROM sysobjects WHERE xtype = 'U' and name ='ProductDocument' |
Crdate es la fecha de creación y sysobjects es la vista que contiene la mayor parte de los objetos de la base de datos, incluyendo tablas, vistas, procedimientos almacenados y funciones.
¿Cómo puedo obtener la lista de todas las tablas en las bases de datos?
Sp_MSforeachdb es un procedimiento almacenado muy útil que ayuda a trabajar con todas las bases de datos. Por alguna razón, este procedimiento almacenado no está documentado.
|
1 2 3 |
DECLARE @cmd varchar(8000) SELECT @cmd = 'USE ? SELECT ''?'',name FROM sysobjects WHERE xtype = ''U''' EXEC sp_MSforeachdb @cmd |
El ? es el nombre de la base de datos. Mostrará el resultado de la consulta en cada base de datos.
¿Cómo puedo buscar una tabla entre todas las bases de datos?
Este es un problema clásico. El usuario creó una tabla, pero no recuerda dónde fue creada y hay múltiples bases de datos donde necesitamos buscar.
El siguiente ejemplo muestra cómo encontrar una tabla llamada test en todas las bases de datos de SQL Server. La consulta mostrará la base de datos y la tabla.
|
1 2 3 |
DECLARE @cmd varchar(8000) SELECT @cmd = 'USE ? SELECT ''?'',name FROM sysobjects WHERE xtype = ''U'' and name=''test''' EXEC sp_MSforeachdb @cmd |
¿Cómo puedo obtener la lista de nombres de procedimientos almacenados y su código?
La vista syscomments contiene información muy útil acerca de los procedimientos almacenados, desencadenadores y otros objetos de SQL Server. Usted puede revisar el código de sus procedimientos, desencadenadores y otros objetos. Sysobjects contiene el nombres del procedimiento almacenado y syscomments, el código. Esta consulta es muy útil para encontrar algunas palabras en múltiples procedimientos almacenados al mismo tiempo.
|
1 2 3 4 5 6 |
SELECT name, [text] FROM sysobjects o INNER JOIN syscomments c ON o.id=c.id WHERE xtype = 'P' |
¿Cómo puedo obtener la fecha de creación de un usuario de base de datos específico?
La vista sysusers contiene información útil acerca de los usuarios en una base de datos. El siguiente ejemplo muestra la fecha de creación del usuario público de base de datos.
|
1 2 |
select createdate from [sys].[sysusers] where name='public' |
Para más información acerca de sysusers, vaya a las referencias.
¿Cómo puedo obtener la fecha de creación de un Inicio de Sesión de SQL Server específico?
El Inicio de Sesión permite ingresar a la base de datos SQL Server y el usuario de base de datos contiene permisos para acceder a bases de datos específicas. La siguiente consulta muestra la Fecha de creación de un Inicio de Sesión específico:
|
1 2 3 |
select * from [sys].[syslogins] where name='sa' |
¿Cómo puedo obtener el Inicio de Sesión y el usuario mapeado en todas las bases de datos?
Esta información está en sys.database.principals y syss.server.principals. Usted encontrará el Inicio de sesión y el usuario de base de datos mapeado a él.
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @cmd varchar(8000) SELECT @cmd = 'SELECT ''?'',login_name = sp.name, user_name = dp.name FROM ?.sys.database_principals AS dp INNER JOIN sys.server_principals AS sp ON dp.[sid] = sp.[sid]' EXEC sp_MSforeachdb @cmd |
¿Cómo puedo detectar la fragmentación de una tabla específica?
Múltiples inserciones y eliminaciones pueden producir fragmentaciones en las bases de datos. En versiones anteriores, las sentencias DBCC SHOWCONTIG eran usadas para detectar fragmentaciones. Ahora usamos la función sys.dm_db_index_physical_stats. La columna que necesita revisar es avg_fragmentation_in_percent. Si el porcentaje es alto, puede que usted necesita reorganizar o reconstruir los índices de su tabla.
El siguiente ejemplo muestra la información de fragmentación acerca de la tabla [Purchasing].[PurchaseOrderDetail].
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks2012'); SET @object_id = OBJECT_ID('[Purchasing].[PurchaseOrderDetail]'); SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED'); GO |
Si él porcentaje de fragmentación es más alto que 30%, es mejor reconstruir el índice. Si es menor que 30%, usted puede simplemente reorganizar el índice.
¿Cómo puedo obtener la información acerca de las sesiones SQL Server?
La vista [sys].[dm_exec_sessions] contiene información útil acerca de las sesiones como el tiempo de inicio, el tiempo de finalización, el nombre del inicio de sesión, el dominio NT, el programa usado y más.
|
1 2 |
SELECT * FROM [sys].[dm_exec_sessions] |
¿Cómo puedo obtener información acerca de la fecha de creación de una copia de seguridad?
Usted puede encontrar la fecha de inicio y la fecha de finalización de una copia de seguridad de base de datos en la tabla backupset.
|
1 2 3 4 5 |
SELECT [backup_start_date], [backup_finish_date] FROM [msdb].[dbo].[backupset] |
¿Cómo puedo obtener información acerca del tamaño en MB y la localización de una copia de seguridad?
Usted también puede encontrar información muy útil acerca del tamaño del archivo de copia de seguridad y la ruta usando la tabla de sistema backupfile. El siguiente ejemplo muestra el tamaño de las copias de seguridad en MB y la ruta donde están localizadas. Esta información es almacenada en la base de datos msdn. El tamaño del archivo de copia de seguridad está almacenado en bytes, es por eso que es necesario convertir el valor a MB.
|
1 2 3 4 5 6 |
SELECT [file_size]/1024/1024 as Size_in_mb ,[physical_name] FROM [msdb].[dbo].[backupfile] |
¿Cómo puedo obtener información acerca de la fecha de creación y la fecha de modificación de un Trabajo de SQL Server?
Hay mucha información acerca de trabajos SQL en la base de datos MSDB, en la tabla sbo.sysjobs. Toda la información relacionada al agente está almacenada en la base de datos MSDB.
|
1 2 3 4 5 |
SELECT [date_created] ,[date_modified] FROM [msdb].[dbo].[sysjobs] |
Esta información también puede ser mostrada usando el procedimiento almacenado help_job
Conclusión
Las vistas/tablas/funciones de sistema nos ayudan a monitorear nuestra base de datos, rastrear cambios y mesurar el desempeño de las bases de datos. Hay múltiples usos de estos objetos para ayudarnos en nuestros problemas de la vida real.
Referencias
- Vistas de esquema de información
- Vista Sys.objects
- Vista Sys.comments
- Procedimiento almacenado sp_MSforeachdb
- Vista Sys.sysusers
- Vista Dbo.backupfile
- Vista Dbo.sysjobs
- Vista Sys.dm_exec_sessions
Realizó consultorias para empresas gubernamentales, empresas petroleras, sitios web, revistas y universidades alrededor del mundo. Daniel participa regularmente en conferencias de SQL Server y blogs. Escribe material sobre SQL Server y exámenes de certificación.
También realiza la traducción de artículos de SQLShack al español
Ver todas las publicaciones de Daniel Calbimonte
- Cómo construir un cubo desde cero usando SQL Server Analysis Services (SSAS) - December 16, 2019
- Fecha de conversión de SQL - December 11, 2019
- Funciones frente a los procedimientos almacenados en SQL Server - October 14, 2019