¿Qué es un Diccionario de Datos?

En SQL Server, el diccionario de datos es un conjunto de tablas de base de datos que se utilizan para almacenar información sobre la definición de una base de datos. El diccionario contiene información sobre objetos de base de datos como tablas, índices, columnas, tipos de datos y vistas.

El diccionario de datos es utilizado por SQL Server para ejecutar consultas y se actualiza automáticamente cada vez que se agregan, eliminan o modifican objetos dentro de la base de datos.

Todos los ejemplos de este artículo se basan en Microsoft SQL Server Management Studio y la base de datos AdventureWorks2012. Puede comenzar a usar estas herramientas gratuitas utilizando mi guía Introducción a SQL Server.

Cómo usa SQL Server el Diccionario de datos

SQL Server utiliza el diccionario de la base de datos para verificar instrucciones SQL. Cuando ejecuta una instrucción SQL, el DBMS (Sistema de Administración de bases de datos) analiza la instrucción y, a continuación, determina si las tablas y los campos a los que hace referencia son válidos. Para hacer esto rápidamente se hace referencia al diccionario de datos.

Además de probar la validez de las sentencias, SQL Server utiliza el diccionario de datos para ayudar con la generación de planes de consulta y para hacer referencia a la información que define la estructura de la base de datos.

El diccionario de datos se convierte en una guía, per se, para que SQL Server la use para acceder a sus datos. En pocas palabras, sin el diccionario de datos, aunque SQL Server conocería y entendería el lenguaje SQL, no sabría sobre las tablas y columnas de su base de datos; por lo tanto, no podría consultarlas.

Otros usos del Diccionario de datos

Dado que el diccionario de datos contiene la definición de la base de datos, es un recurso realmente bueno para que usted lo use para obtener información sobre la base de datos. Lo realmente genial es que el diccionario de datos se compone de tablas y vistas SQL. Esto significa que puede obtener información sobre la base de datos a través de consultas.

Los diccionarios de datos son utilizados por diseñadores y desarrolladores para comprender la estructura de la base de datos. Puede pensar en el diccionario como un documento de referencia actualizado.

Las herramientas de diseño, como SQL Server Management Studio, muestran información sobre las bases de datos a través del explorador de objetos mediante el diccionario de datos.

Las tablas enumeradas anteriormente no se conocen mágicamente, sino que el explorador de objetos emitió una consulta al diccionario de datos para recuperar todas las tablas de usuario.

El diccionario de datos se almacena en una serie de tablas del sistema. Aunque puede consultar directamente estas tablas, Microsoft se reserva el derecho de modificar las tablas del sistema que componen el diccionario de datos. Debido a esto, recomiendan consultar las vistas INFORMATION_SCHEMA en lugar de acceder directamente a las tablas.

Dado que puede consultar el diccionario de datos usted mismo, puede responder algunas preguntas que de otro modo requerirían mucha caza y picoteo a través del explorador de objetos. Por ejemplo, ¿cómo puede encontrar fácilmente todas las tablas y vistas utilizando la columna BusinessEntityID? Sin el diccionario de datos, tendrá que usar el explorador de objetos y abrir todas y cada una de las tablas y ver y revisar las definiciones de la columna. Sin embargo, utilizando el diccionario de datos, esto se puede hacer mediante una consulta simple.

Vistas INFORMATION_SCHEMA

Las vistas INFORMATION_SCHEMA incluidas en SQL Server cumplen con el estándar ISO SQL-92. Esto significa que otros proveedores de bases de datos que cumplan con la norma ISO proporcionarán el mismo conjunto de vistas.

Estas son algunas de las vistas más utilizadas y sus descripciones:COLUMNAS

• : Devuelve una fila por cada columna que el usuario actual tiene acceso a usar en la base de datos actual. Esta vista se puede usar para determinar el tipo de datos y la tabla para la que se define la columna.TABLAS
• – Devuelve una fila por cada tabla que los usuarios tienen acceso a usar dentro de la base de datos actual. Nota: tanto las tablas como las vistas se devuelven mediante la vista TABLAS.
• VIEW_TABLE_USAGE-Devuelve una fila para cada tabla que se utiliza en una vista dentro de la base de datos actual.VISTAS
• : Devuelve una fila para las vistas a las que se puede acceder utilizando los permisos del usuario en curso desde la base de datos actual.

Ejemplos utilizando vistas INFORMATION_SCHEMA

Enumerar todas las tablas que incluyen una columna especificada

Puede hacer esto utilizando INFORMATION_SCHEMA.Vista DE columnas. Por ejemplo, las siguientes listas de todas las tablas y vistas contienen la columna BusinessEntityID

SELECT TABLE_NAMEFROM AdventureWorks2012_Data.INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME = 'BusinessEntityID'

¡Como puede imaginar, el diccionario de datos puede ser útil! ¿Te imaginas tener que buscar cada definición a mano para encontrar toda la ocurrencia de un campo dentro de la base de datos?

Enumere todas las tablas de una base de datos

Utilice el comando INFORMATION_SCHEMA.Vista de TABLAS para hacer esto.

SELECT TABLE_NAME, TABLE_TYPEFROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLESORDER BY TABLE_NAME

Esta consulta devuelve tanto tablas base como vistas.

Lista el número de tablas de Cada esquema

En este ejemplo, se enumeran cada esquema y el número de tablas y vistas definidas en ellos:

SELECT TABLE_SCHEMA, Count(TABLE_SCHEMA)FROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLESGROUP BY TABLE_SCHEMA

Vistas de catálogo de SQL Server

Las vistas de catálogo son otra forma de ver el diccionario de datos. Creo que las vistas del catálogo de objetos proporcionan más información y tiendo a usar estas vistas sobre las vistas INFORMATION_SCHEMA.

Algunas de las vistas que he encontrado más útiles incluyen: objetos

• : contienen una fila para cada objeto, como una restricción de CLAVE EXTERNA o CLAVE PRIMARIA definida dentro de la base de datos.columnas
• : contienen una fila para cada columna de un objeto, como una vista o tablas.tablas
• : devuelve una fila para cada objeto de tabla

Ejemplos que utilizan vistas de catálogo

Los ejemplos siguientes utilizan las vistas de catálogo de Microsoft SQL Server para obtener la misma información que se utilizó anteriormente con INFORMATION_SCHEMA.

Listar todas las tablas que incluyen una columna especificada

Para obtener los nombres de las tablas que utilizan una columna específica, tanto el sistema.tablas y sys.la vista de columnas se debe usar en conjunto. Se unen a través del object_id, que se usa para identificar objetos comunes de la base de datos, como tablas y vistas.

SELECT t.name, t.type_descFROM AdventureWorks2012_Data.sys.columns AS cINNER JOIN sys.tables AS t ON c.object_id = t.object_idWHERE c.name = 'BusinessEntityID'

Listar todas las tablas de una base de datos

El sistema.la vista de tablas se puede utilizar para obtener los nombres de todas las tablas definidas en la base de datos. Esta vista devuelve tablas base y vistas.

SELECT name, type_descFROM AdventureWorks2012_Data.sys.tablesORDER BY Name

Enumere el número de tablas en Cada Esquema

El sistema.la vista de tablas no incluye un nombre de esquema, por lo que se utiliza la función integrada SCHEMA_NAME para obtenerlo.

SELECT SCHEMA_NAME(schema_id), count(name)FROM AdventureWorks2012_Data.sys.tablesGROUP BY SCHEMA_NAME(schema_id)

Como puede ver en el ejemplo, es un poco más técnico que usar INFORMATION_SCHEMA. Sin embargo, digo por experiencia que hay mucha más información contenida en las vistas de catálogo: Si tiene una pregunta en particular sobre la estructura de una base de datos, esta es la tabla a la que debe acceder.