Daniel Calbimonte

Las vistas/tablas/funciones de sistema de SQL Server. Preguntas frecuentes y soluciones a problemas de la vida real

February 28, 2017 by

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

Opción 2

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

Opción 2

[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:


[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:

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.

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.

¿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.

¿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.

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:

¿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.

¿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].

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.

¿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.

¿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.

¿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.

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


Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte es un Microsoft Most Valuable Professional, Microsoft Certified Trainer y Microsoft Certified IT Professional para SQL Server. Es autor experto de SSIS, profesor en IT Academies y tiene más de 13 años de experiencia trabajando con diferentes bases de datos.

Él ha trabajado para el gobierno, compañías de petróleo, sitios web, revistas y universidad alrededor del mundo. Daniel también habla regularmente en conferencias de SQL Server y blogs. Él también es un escritor para material de entrenamiento de SQL Server para exámenes de certificación.

Ver todas las publicaciones de Daniel Calbimonte
Daniel Calbimonte
Comandos SQL

Acerca de Daniel Calbimonte

Daniel Calbimonte es un Microsoft Most Valuable Professional, Microsoft Certified Trainer y Microsoft Certified IT Professional para SQL Server. Es autor experto de SSIS, profesor en IT Academies y tiene más de 13 años de experiencia trabajando con diferentes bases de datos. Él ha trabajado para el gobierno, compañías de petróleo, sitios web, revistas y universidad alrededor del mundo. Daniel también habla regularmente en conferencias de SQL Server y blogs. Él también es un escritor para material de entrenamiento de SQL Server para exámenes de certificación. Ver todas las publicaciones de Daniel Calbimonte

7,851 Views