Documenta fácilmente tus BBDD

Este será el primero de una serie de artículos que, combinándolos entre ellos, podrás obtener la documentación de tu base de datos o incluso de tu modelo tabular de manera sencilla y automáticamente, en un simple clic.

En este primer artículo voy a abordar el cómo documentar una base de datos de SQL Server, de forma que la información esté contenida en la propia base de datos y pueda ser explotada por otras herramientas.

Empezaré hablando de las propiedades extendidas de los objetos de SQL Server.

¿Qué son?

Se trata de unas etiquetas del tipo “Clave – Valor” que puedes asociar a cada uno de los objetos de la base de datos.

¿Para qué sirven?

Para lo que tú quieras o necesites. Puedes usar para clasificar los objetos, añadir descripciones, explicaciones, etc. Aportan la información adicional que tu quieras sobre el objeto al que están asociados.

¿Cómo accedo a ellas?

Puedes acceder usando la interfaz: btn derecho –> propiedades –> propiedades extendidas.

También puedes usar la dmv sys.extended_properties para consultarlas. Puedes encontrar toda la información sobre esta vista aquí.

Ej: Obtener todas las tablas con su descripción si la tiene

Select 
	SCHEMA_NAME(schema_id) as [SCHEMA]
	,t.[name] as [TABLE]
	,p.value as [MS_Description]
from sys.tables t
	left join sys.extended_properties p
		on p.major_id = t.object_id and p.name ='MS_Description'
			and p.minor_id = 0

Ej: Obtener todas las columnas con su descripción si la tienen (quitando las del esquema sys)

Select 
	OBJECT_SCHEMA_NAME (object_id) as [SCHEMA]
	,OBJECT_NAME (object_id) as [TABLE]
	,c.name as [COLUMN]
	,p.Value as [MS_Description]
from sys.columns c
			left join sys.extended_properties p
				on p.major_id = c.object_id and c.column_id = p.minor_id
				and p.name ='MS_Description'
WHERE OBJECT_SCHEMA_NAME (object_id) <> 'sys'
ORDER by 1,2,column_id

O puedes usar las funciones del sistema para interactuar con ellas:

• sp_addextendedproperty : Permite agregar una propiedad extendida a un objeto (ver)
• fn_listextendedproperty: Devuelve los valores de propiedad extendidas del objeto (ver)
• sp_dropextendedproperty: Quita una propiedad extendida existente (ver)
• sp_updateextendedproperty: Actualiza el valor de una propiedad extendida existente (ver)

Ejemplos prácticos

A parte de para incluir la descripción, que normalmente se utiliza la clave “MS_Description” para ello, yo lo he usado para indicar cosas como:

• Cuál es el Alias de una tabla
• Cuáles son las columnas por las que se particiona una tabla
• El tipo de SCD (Slow Changing Dimension) de una columna
• Qué tablas son las tablas maestras (es decir, las que deberían tener valores si creáramos desde 0 la base de datos)
• …

Truquillo

Para interactuar con las propiedades extendidas, yo he descubierto que es más cómodo informarlas en un Excel.

Aquí os dejo un ejemplo de cómo suelo hacerlo ( igual hay una forma mejor de hacerlo, pero por ahora me arreglo bien con esta). En este ejemplo sólo miro tablas y columnas, pero se podrían añadir vistas, u otros objetos.

Cuando os descarguéis el ejemplo, veréis que tiene 6 pestañas:

• Tablas: El listado de tablas con su esquema y por cada propiedad extendida una columna. También incluye la “Query” que habría que lanzar en la base de datos correspondiente (en el ejemplo es AdventureWorks2022).
• Documentación tablas: Incluye una tabla que referencia a la pestaña anterior y luego le añado las siguientes columnas:
  • Rename: si quiero cambiar el nombre de la tabla lo incluyo aquí
  • Script Rename: construye el script para renombrar la tabla.
  • Script Description: construye el script para añadir o actualizar la propiedad extendida de la tabla, usando los datos de la siguiente pestaña (Scripts Tablas).

Cuando quiero cambiar una descripción, relleno / cambio la columna MS_Description y luego sólo tengo que ejecutar todos los scripts en la BBDD y listo.

• Scripts Tabla: Aquí incluyo en cada celda el script que quiero que se genere, mediante fórmulas de Excel. Por cada propiedad extendida diferente, tendría que generar el script que necesite.
• Columnas: El listado de columnas, con su respectiva tabla y descripción. Además, también incluyo el quiere que genera este listado.
• Documentación columnas: al igual que con las tablas, referencio la tabla que hay en la pestaña de columnas y luego añado las siguientes columnas:
  • Renamed Table: Por si se ha renombrado la tabla (para poder construir los scripts bien)
  • Renamed column: Por si quiero renombrar la columna.
  • Table2: (Campo oculto) Si se ha renombrado la tabla, se informa ese, sino el original. Lo utilizo para construir de forma más sencilla las fórmulas en el Excel.
  • Column2: (Campo oculto) Si se ha renombrado la columna, se informa ese, sino el original. Lo utilizo para construir de forma más sencilla las fórmulas en el Excel.
  • IsView: Para indicar si es una vista o una tabla, ya que necesito saberlo a la hora de construir los otros scripts. Aquí yo parto del hecho de que en mis bases de datos si es una vista, lleva “View” en el nombre.
  • Script Rename: Si rellenas el renombrado de columna, se informará
  • Script Description: construye el script para añadir o actualizar la propiedad extendida de la columna, usando los datos de la siguiente pestaña (Scripts Columnas).

Cuando quiero cambiar una descripción, relleno / cambio la columna MS_Description y luego sólo tengo que ejecutar todos los scripts en la BBDD y listo.

• Script Columnas: Aquí incluyo en cada celda el script que quiero que se genere, mediante fórmulas de Excel. Por cada propiedad extendida diferente, tendría que generar el script que necesite.

Conclusión

Como ves, las propiedades extendidas son sencillas de utilizar y pueden añadir mucho valor a tu base de datos, y además puedes apoyarte en herramientas externas, como el Excel, para facilitarte mucho la vida. ¡No las desaproveches!