Generar un Diccionario de Datos en SQL Server

Documentación no es de las palabras más agradables en el mundo del desarrollo de software. Para los que trabajan con Bases de Datos suelen ser similar los retos de la documentación, donde uno de los puntos más importantes es conocer dónde está tu información. Con este propósito vamos a ver cómo gestionar un Diccionario de Datos en SQL Server.

¿Qué contiene un Diccionario de Datos?

No podría hablarte de un estándar puesto que dependiendo de las necesidades o formatos, podemos encontrar varios en diferentes lugares.

Algunas documentaciones incluyen dentro del llamado Diccionario de Datos al Modelo Entidad Relación, su gráfica y una descripción de todas las relaciones que puedan existir.

No considero que exista una sola respuesta, así que dejaré un poco abierto este punto a criterio de cada uno e impulsando puedas buscar mucha más información en todo lo que es documentación de software.

¿Qué hacemos entonces?

Quiero ayudarte a que puedas usar la misma metada de tus bases de datos para generar reportes de tus tablas y sus estructuras.

Será así de sencillo. Puedes consultar las vistas:

• sys.tables
• sys.columns
• sys.systypes
• sys.objects
• sys.extended_properties

Donde quiero enfocarme en la generación de un reporte para que puedas documentar la estructura de tus tablas.

Antes de mostrarte la forma de generar los datos

¿Has utilizado alguna vez las descripciones de campos en el mismo SSMS? Puedes describir cada columna de tus tablas y guardar esa metadata dentro de la base de datos. Es lo que se conoce como extended properties.

Haz un click derecho sobre la columna que quieres añadir la descripción, busca las propiedades y llena los campos descipción como se ve en las imágenes.

Es una alternativa para documentar las descripciones de tus columnas. Muy bien, sé que puede no parecerte tan cómodo hacerlo. ¿Lo bueno? Que puedes utilizar este dato para la ejecución del código a continuación.

Genera un Diccionario de Datos

Ejecuta este código o puedes llevarlo dentro de un Procedimiento Almacenado para ejecutarlo cuando quieras.

SELECT 
	'DATADICTIONARY' AS [REPORT],
	@@SERVERNAME AS [ServerName],
	DB_NAME() AS [DatabaseName],
	t.name AS [TableName], 
	schema_name(t.schema_id) AS [SchemaName], 
	c.name AS [ColumnName], 
	st.name AS [DataType], 
	c.max_length AS [MaxLength], 
	CASE 
		WHEN c.is_nullable = 0 THEN 'NO'
		ELSE 'YES'
	END AS [IsNull],
	CASE 
		WHEN c.is_identity = 0 THEN 'NO'
		ELSE 'YES'
	END AS [IsIdentity], 
	isnull(ep.value, '-- add description here') AS [Description]
FROM [sys].[tables] t
INNER JOIN [sys].[columns] c
	ON t.object_id= c.object_id 
INNER JOIN [sys].[systypes] st 
	ON c.system_type_id= st.xusertype 
INNER JOIN [sys].[objects] o 
	ON t.object_id= o.object_id 
LEFT JOIN [sys].[extended_properties] ep 
	ON o.object_id = ep.major_id 
	AND c.column_Id = ep.minor_id
WHERE t.name <> 'sysdiagrams' 
ORDER BY 
	t.name,
	c.column_Id

Así obtenemos un diccionario para la documentación que necesitemos. ¿Ves el campo de la descripción al final? Esto es lo que se obtiene si utilzas los extended properties que viste más arriba. Si no los has usado, estos valores se devolverán como nulos y el script adicionará un comentario para que puedas llenar datos manualmente en el lugar que desees exportar.

Puedes ver también cuál es La Manera Más Sencilla de Exportar datos desde SQL Server.

Documentar el Diccionario de Datos en SQL Server

Estos pasos te ayudarán a ganar tiempo en la documentación de tus programas. Puedes ver que luego de ejecutar el script, solamente te toca llenar las descripciones de los campos si no lo has hecho en el SSMS.

Estos datos pueden ayudarte a hacer también una estimación de los tamaños de las tablas que vas a utilizar. A partir de esta información podrías realizar ciertos análisis y proyecciones de crecimiento.

Mantener la documentación de software actualizada es un reto, pero verás es muy útil en el tiempo.