Hace unos días, en nuestro canal de telegram surgió un tema interesante sobre documentación de tablas y sus columnas en SQL Server. Una de las formas más sencillas para documentar nuestros objetos en SQL Server de forma nativa (sin recurrir a aplicaciones de terceros) es el uso de las propiedades extendidas. Personalmente pienso que, si no tienes una herramienta de documentación externa, las propiedades extendidas son una alternativa indispensable para cualquier DBA o trabajador de datos. En este artículo, veremos qué son las propiedades extendidas, sus usos principales, y cómo podemos mejorar la gestión y documentación de nuestros entornos de SQL Server.
¿Qué son las Propiedades Extendidas en SQL Server?
En resumidas cuentas, las propiedades extendidas son metadatos adicionales que podemos asociar a objetos dentro de SQL Server. Estas propiedades, presentes en SQL Server desde SQL 2008, son unas etiquetas clave-valor que nos van a permitir almacenar información extra, como descripciones, anotaciones y cualquier dato relevante que consideremos útil para la administración y documentación de nuestra base de datos. No te creas que esto es todo, tú en tu escenario le vas a poder dar cualquier uso que te imagines y que sea compatible con una etiqueta clave-valor.
Usos Comunes de las Propiedades Extendidas
Como ya hemos adelantado, las propiedades extendidas tienen tantos usos como tu creatividad logre imaginar. Os voy a contar los principales con los que yo me he encontrado a lo largo de estos años.
- Documentación de Objetos: Podemos usar las propiedades extendidas para la documentación de tablas, columnas, procedimientos almacenados, funciones y otros objetos de manera que esta tarea se vuelva más manejable y, sobre todo, la información se almacene en la propia base de datos.
- Auditoría y Cumplimiento: De la misma manera, se pueden utilizar las propiedades extendidas para almacenar información de auditoría y cumplir con regulaciones específicas como etiquetado de datos confidenciales o sensibles.
- Metadatos Personalizados: Más de lo mismo, podemos usar propiedades extendidas para agregar metadatos específicos de nuestra organización y que de otra manera no estarían cubiertos por los atributos estándar de SQL Server.
Documentando objetos en SQL Server con Propiedades Extendidas
Como hemos visto, uno de los usos más comunes y útiles de las propiedades extendidas es la documentación de objetos. Esta capacidad no solo nos facilita el mantenimiento, sino que también mejora la colaboración entre equipos de desarrollo y los administradores de las bases de datos. Al tener documentado quien creó una tabla o que proceso o persona se encarga de alimentar los datos, por ejemplo, los DBAs vamos a tener más fácil encontrar a las personas afectadas ante una incidencia. O, si sabemos quien desarrolló ese procedimiento que va lento, vamos a poder tomar medidas correctivas (siempre respetando los derechos humanos, recordad).
Documentación de Tablas y Columnas
Podemos agregar descripciones detalladas a nuestras tablas y columnas para aclarar su propósito y uso. Por ejemplo, podemos describir la función de una columna específica, lo que es particularmente útil en bases de datos grandes y complejas.
Ejemplo Práctico
Supongamos que tenemos una tabla Clientes y queremos documentar su columna EmailAlternativo. Podemos hacerlo con el siguiente script:
EXEC sp_addextendedproperty
@name = N'MS_Description',
@value = N'Correo electrónico del cliente para contactar en caso de que el principal devuelva un error',
@level0type = N'SCHEMA', @level0name = 'dbo',
@level1type = N'TABLE', @level1name = 'Clientes',
@level2type = N'COLUMN', @level2name = 'EmailAlternativo';Con este script, hemos agregado una propiedad extendida a la columna EmailAlternativo de la tabla Clientes en el esquema dbo, describiendo que almacena el correo electrónico del cliente alternativo. Como siempre pasa, cuanto más descriptivas sean las descripciones más fácil será entenderlas.
Documentación de Vistas, Procedimientos Almacenados y Funciones
Las vistas, procedimientos almacenados y funciones también pueden beneficiarse de las propiedades extendidas. Podemos documentar su propósito, parámetros y cualquier otra información relevante que facilite su uso y mantenimiento.
Ejemplo Práctico
Consideremos un procedimiento almacenado llamado usp_ObtenerClientesActivos. Podemos documentar su propósito con el siguiente script:
EXEC sp_addextendedproperty
@name = N'MS_Description',
@value = N'Obtiene una lista de todos los clientes activos en el sistema',
@level0type = N'SCHEMA', @level0name = 'dbo',
@level1type = N'PROCEDURE', @level1name = 'usp_ObtenerClientesActivos';
EXEC sp_addextendedproperty
@name = N'MS_Author',
@value = N'Ataulfo Contreras’,
@level0type = N'SCHEMA', @level0name = 'dbo',
@level1type = N'PROCEDURE', @level1name = 'usp_ObtenerClientesActivos';
En este caso, el script añade una descripción clara y concisa sobre el propósito del procedimiento usp_ObtenerClientesActivos. Después, un segundo script, añade el autor del procedimiento a otra propiedad extendida.Gestión de Propiedades Extendidas
La forma sencilla de gestionar las propiedades extendidas es a través del entorno gráfico, con un clic derecho sobre el objeto al que deseemos añadir una propiedad extendida podremos entrar en la ventana de propiedades y ahí encontraremos el apartado para las propiedades extendidas. Sin embargo esto no es rápido cuando tienes que ir columna a columna de una tabla documentando sus atributos. Para ello podremos usar los procedimientos almacenados de sistema.
Crear Propiedades Extendidas
Para añadir una propiedad extendida, utilizaremos el procedimiento almacenado sp_addextendedproperty. Este procedimiento nos permite especificar el nombre y el valor de la propiedad, así como el objeto al que se va a asociar.
Modificación de Propiedades Extendidas
Si necesitamos actualizar una propiedad extendida existente, utilizaremos el procedimiento sp_updateextendedproperty. Este procedimiento nos permite cambiar el valor de una propiedad extendida sin necesidad de eliminarla y volver a crearla.
Eliminación de Propiedades Extendidas
Para eliminar una propiedad extendida, utilizaremos el procedimiento sp_dropextendedproperty. Este procedimiento elimina completamente la propiedad especificada del objeto.
Consultar las Propiedades Extendidas existentes
La finalidad de las propiedades extendidas siempre va a ser poder consultarlas, si no para qué íbamos a estar haciendo todo este trabajo, ¿verdad?. En este sentido Microsoft pone a nuestra disposición la función sys.fn_listextendedproperty pero su uso está muy limitado y a mi no me gusta. Existen otras herramientas externas capaces de usar las propiedades extendidas para la generación automática de documentación, como SQL Server Data Tools (SSDT) y otros sistemas de documentación de bases de datos. Estas herramientas pueden extraer las propiedades extendidas y generar documentación completa y detallada de la base de datos. Sin embargo, como el objetivo de este post es no salirnos de SQL y cómo sabéis que yo soy partidario de usar las tablas y vistas de sistema, en este caso no iba a ser menos. Os dejo el script que yo uso:
SELECT
CASE
WHEN indexes.name IS NOT NULL AND Child.type_desc = 'USER_TABLE' THEN 'TABLE_INDEX'
WHEN indexes.name IS NOT NULL AND Child.type_desc = 'VIEW' THEN 'VIEW_INDEX'
WHEN extended_properties.minor_id <> 0 AND Child.type_desc = 'USER_TABLE' THEN 'TABLE_COLUMN'
WHEN extended_properties.minor_id <> 0 AND Child.type_desc = 'VIEW' THEN 'VIEW_COLUMN'
ELSE Child.type_desc
END AS Object_Type,
extended_properties.name AS Extended_Property_Name,
CAST(extended_properties.value AS NVARCHAR(MAX)) AS Extended_Property_Value,
schemas.name AS Schema_Name,
Child.name AS Object_Name,
Parent.name AS Parent_Object_Name,
columns.name AS Parent_Column_Name,
indexes.name AS Index_Name
FROM sys.extended_properties
INNER JOIN sys.objects Child
ON extended_properties.major_id = Child.object_id
INNER JOIN sys.schemas
ON schemas.schema_id = Child.schema_id
LEFT JOIN sys.objects Parent
ON Parent.object_id = Child.parent_object_id
LEFT JOIN sys.columns
ON Child.object_id = columns.object_id
AND extended_properties.minor_id = columns.column_id
AND extended_properties.class_desc = 'OBJECT_OR_COLUMN'
AND extended_properties.minor_id <> 0
LEFT JOIN sys.indexes
ON Child.object_id = indexes.object_id
AND extended_properties.minor_id = indexes.index_id
AND extended_properties.class_desc = 'INDEX'
ORDER BY Child.type_desc ASC;Conclusión
Las propiedades extendidas en SQL Server son una opción muy útil y válida para la documentación de nuestras bases de datos. Nos permiten añadir metadatos personalizados a nuestros objetos, mejorando la claridad, la colaboración y el cumplimiento normativo. Ya sea que estemos documentando tablas, columnas, procedimientos almacenados, funciones o cualquier otro objeto, las propiedades extendidas nos facilitan una gestión más eficiente y eficaz de nuestros datos en SQL Server. Además, la capacidad de automatizar la adición y gestión de propiedades extendidas mediante scripts de SQL amplía aún más sus aplicaciones, permitiéndonos mantener nuestras bases de datos bien documentadas y organizadas sin un esfuerzo manual excesivo. Aprovechando estas características, podemos transformar nuestras bases de datos en repositorios de información clara y útil, accesibles para todos los miembros de nuestro equipo y para las aplicaciones que interactúan con ellas.
En resumen, no solo conocer sino dominar el uso de las propiedades extendidas es una habilidad importante para cualquiera que busque optimizar la gestión y documentación de sus bases de datos en SQL Server. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!
