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 2008 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
Hola, y como puedo hacer para borrar esas propiedades extendidas, lo que pasa es que migre datos de access a sql server 2008 y cuando genero el scrip de sql me trae gran cantidad de codigo, y a mi consideracion es codigo basura, pero se que es propiedades extendidas que se formo por la importacion, pero quiero borrarlas, para que cuando genere nuevamente el scrip me lo genere limpio
Hola, debes usar el procedimiento almacenado sp_dropextendedproperty. Aquí un link con mayor información.
https://msdn.microsoft.com/es-pe/library/ms178595.aspx
Saludos
Este script de diccionario de datos NO aplica para sql server 2005
Luis, de hecho fue preparado en SQL 2008 así que probablemente no funcione, vamos a probarlo