El diccionario de datos es un repositorio de metadatos que contiene las definiciones de los objetos de datos, descripciones y relaciones entre si. El diccionario es muy importante en el desarrollo de aplicaciones, ya que permite a los involucrados en el desarrollo conocer a detalle las bases de datos.
Lamentablemente esta información no siempre esta disponible, y si lo está, se encuentra incompleta o desactualizada.
Una forma práctica de gestionar el diccionario de datos es mantenerlo completo en la misma base de datos. A partir de la versión 2005 de SQL Server se mejoró mucho el manejo de metadatos y se cuenta con información suficiente de fácil acceso para obtener toda la definición de las tablas, vistas, etc.
En este artículo vamos a mostrar unos scripts sencillos para obtener las definiciones de las tablas de una base de datos y como actualizar las descripciones de tablas y columnas de las tablas. Vamos a comenzar con la creación de una base de datos muy simple para tomarla como ejemplo:
para bajar los scripts completos, haga clic aquí
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
--Creación de base de datos de prueba CREATE DATABASE PruebaDiccionario GO USE PruebaDiccionario GO --creación de la primera tabla de prueba CREATE TABLE Diccionario1 ( Código smallint NOT NULL, Nombre varchar(50) NOT NULL, Documento char(8) NOT NULL, Teléfono varchar(10) NULL, Notas varchar(max) NULL, Estado bit NOT NULL) GO --creación de la segunda tabla de prueba CREATE TABLE Diccionario2 ( NumeroRegistro int NOT NULL, FechaRegistro date NOT NULL, Cantidad smallint NOT NULL, Descuento decimal(5,2) NULL, Monto decimal(15,4) NOT NULL) GO |
Luego de crear las tablas de prueba ejecutamos la siguiente consulta para obtener el diccionario:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SELECT SCHEMA_NAME(o.schema_id) AS Esquema, O.Name AS Tabla, P1.Value AS [Descripción Tabla], C.Name AS Columna, T.Name AS Tipo, C.max_length AS Longitud, C.[Precision] AS Presición, C.scale AS Escala, CASE WHEN C.Is_Nullable = 0 Then 'No' WHEN C.Is_Nullable = 1 Then 'Si' END [Nulo?], P2.value AS [Descripción Col] FROM sys.tables O INNER JOIN sys.Columns C ON O.object_id = C.object_id INNER JOIN sys.Types T ON C.system_type_id = T.system_type_id AND C.system_type_id = T.user_type_id LEFT JOIN sys.extended_properties P1 ON C.object_id = P1.major_id AND P1.minor_id = 0 LEFT JOIN sys.extended_properties P2 ON C.object_id = P2.major_id AND C.Column_id = P2.minor_id AND P2.Class = 1 ORDER BY O.Name, C.Column_id GO |
El resultado obtenido es el siguiente:
Se observa en el resultado la estructura de las tablas, pero las columnas Descripción Tabla y Descripción Col contienen datos nulos. Es aquí donde hay que ponerle un poco de trabajo para poder actualizar la descripción de todas las tablas y columnas. Pero es un trabajo de una sola vez. Después de eso solo toca actualizar.
Para poder actualizar las descripciones de tabla y columnas se usa el procedimiento almacenado del sistema sp_addextendedproperty, el cual permite actualizar las propiedades extendidas de un objeto, como por ejemplo, la descripción. A continuación un ejemplo de como agregar la descripción de la tabla Diccionario1:
|
1 2 3 4 5 6 7 8 9 |
--Ingresar Descripcion de Tabla EXEC sys.sp_addextendedproperty @name='MS_Description', @value='Tabla 1 de prueba de diccionario de datos' , @level0type='SCHEMA', @level0name='dbo', @level1type='TABLE', @level1name='Diccionario1' GO |
A continuación un ejemplo de como agregar la descripción de columna Documento de la tabla Diccionario1:
|
1 2 3 4 5 6 7 8 9 10 11 |
--Ingresar Descripción de campo EXEC sp_addextendedproperty @name='MS_Description', @value='Documento de Identidad' , @level0type='SCHEMA', @level0name='dbo', @level1type='TABLE', @level1name='Diccionario1', @level2type='COLUMN', @level2name='Documento' GO |
Volvemos a ejecutar la consulta para obtener el diccionario y este es el resultado:
Obsérvese que la la columna Descripción Tabla ya contiene información para la tabla Diccionario1 y la columna Descripción Col para la columna Documento. Estos pasos hay que repetirlos para las demás tablas y columnas. Podemos ayudarnos con una hoja de cálculo para armar los script de actualización.
Para modificar una propiedad extendida se usa el procedimiento almacenado del sistema sp_updateextendedproperty y para eliminar una propiedad extendida se usa el procedimiento almacenado del sistema sp_dropextendedproperty.
Otros enlaces de interés:
Exelente articulo, gracias
De nada! luego vamos a complementa este articulo con un script para incluir también las llaves primarias y foráneas.
Saludos
Muy buenooo!!!
Hola Alberto, que bueno que tengan un portal como este.
Muy interesante la informacion y sobretodo los cursos que estan lanzando, no tenia noticias tuyas desde el curso en que participe en el 2007. A Nicolas lo conoci en Cibertec y desde alli me doy tiempo de leer la informacion que publica.
Espero poder participar nuevamente en los cursos de SQL Server 2012.
Un fuerte abrazo y exitos en este 2013!!
Saludos a Nicolas.
Gracias Abel, ojalá podamos encontrarnos pronto en algún curso
Saludos