¡Hola, chicos!
¿Estás bien?
En este post me gustaría comentar algo muy importante en el día a día de los DBA y Query Developers, que es la documentación de bases de datos. Rara vez veo entornos donde las columnas o tablas tengan una descripción clara de de qué se trata este objeto de base de datos.
Introducción
Para quienes crean consultas todo el día, como los analistas de BI, esta información facilita mucho la comprensión de las consultas y la precisión de la información. Para insertar esta información en la base de datos usaremos una característica muy antigua de SQL Server, pero que pocas personas usan o incluso conocen, que es la Propiedad Extendida.
Utilizando procedimientos del sistema, podemos describir los objetos de la base de datos usando sus propias palabras para que otras personas puedan entenderlos más fácilmente.
Una vez que estas descripciones se ingresan en la base de datos, puede usar herramientas para visualizar esta información a medida que desarrolla sus consultas o incluso generar documentación completa de la base de datos a partir de las descripciones de propiedad extendida.
A diferencia de algunas herramientas de documentación de bases de datos, esta característica almacena las descripciones en la propia base de datos, lo que significa que el tiempo dedicado a registrar estas descripciones no se pierde si desea cambiar la herramienta de documentación, y estas descripciones se guardarán en un lugar seguro con copias de seguridad (su banco tiene una copia de seguridad, ¿verdad?).
Cómo documentar bases de datos de SQL Server
Para la documentación de las bases de datos de SQL Server, usaremos el recurso llamado Propiedad extendida y procedimientos del sistema. sp_addextendedproperty, sp_updateextendedproperty y sp_dropextendedproperty.
Para poder utilizar estos procedimientos, el usuario debe estar en los roles de base de datos db_owner o ddl_admin (este rol no permite agregar descripciones para la propia base de datos, usuarios o roles) o tener el privilegio ALTER/CONTROL sobre los objetos a los que desea agregar descripciones. Y, por supuesto, los usuarios de la función de servidor, como sysadmin, también pueden utilizar estos procedimientos.
Los tipos de objetos que se pueden documentar utilizando la propiedad extendida utilizando estos SP del sistema (@nivel1tipo) son: AGREGADO, PREDETERMINADO, FUNCIÓN, NOMBRE DE ARCHIVO LÓGICO, PROCEDIMIENTO, COLA, REGLA, SINÓNIMO, TABLA, TIPO_TABLA, TIPO, VISTA y COLECCIÓN DE ESQUEMA XML.
Los subtipos de objetos que se pueden documentar (@nivel2tipo) son: COLUMNA, RESTRICCIÓN, NOTIFICACIÓN DE EVENTO, ÍNDICE, PARÁMETRO y DISPARADOR. Los subtipos (@level2type) dependen del tipo (@level1type) para definirlos, por ejemplo, para documentar una columna, donde debes definir @level1type = 'TABLE' y @level2type = 'COLUMN', haciendo referencia a qué tabla forma parte esta columna.
Para facilitar el uso de estos procedimientos, proporcionaré aquí algunos procedimientos que verificarán si el objeto en cuestión ya tiene una Propiedad extendida y, de ser así, usará sp_updateextendedproperty o, en caso contrario, usará sp_addextendedproperty.
stpExtendedProperty_Table
Ver código fuentestpExtendedProperty_Column
Ver código fuentestpExtendedProperty_Trigger
Ver código fuentestpExtendedProperty_View
Ver código fuentestpExtendedProperty_Procedure
Ver código fuentestpExtendedProperty_Function
Ver código fuentestpExtendedProperty_User
Ver código fuentestpExtendedProperty_Database
Ver código fuente¿Puedo documentar objetos usando una interfaz?
Además de permitirte documentar los objetos de tu base de datos usando una línea de código, también puedes agregar metadatos de Propiedad Extendida usando la interfaz SSMS (SQL Server Management Studio) y es muy simple, aunque no permite automatización:
Ejemplo de propiedad extendida en una tabla
Ejemplo de propiedad extendida en una columna
Ejemplo de formulario para ingresar metadatos y documentar el objeto en cuestión
¿Cómo exportar documentación ya realizada?
Con el siguiente script, puede generar y exportar fácilmente todos los metadatos de propiedad extendida que tenga en la base de datos deseada. Esto es útil para generar un script y aplicarlo a otra instancia de su entorno sin tener que restaurar la base de datos para hacerlo.
SELECT
'EXEC sys.sp_addextendedproperty @name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
WHERE
class_desc = N'DATABASE';
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.schemas B ON A.major_id = B.schema_id
WHERE
A.class_desc = N'SCHEMA';
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.tables A
INNER JOIN sys.schemas B ON A.schema_id = B.schema_id
INNER JOIN sys.extended_properties C ON A.object_id = C.major_id
WHERE
C.class = 1
AND C.minor_id = 0
AND
(
C.value <> '1'
AND C.value <> 1
);
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''COLUMN'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.columns B ON A.major_id = B.object_id AND A.minor_id = B.column_id
INNER JOIN sys.tables C ON A.major_id = C.object_id
INNER JOIN sys.schemas D ON C.schema_id = D.schema_id
WHERE
A.class = 1
AND
(
A.value <> '1'
AND A.value <> 1
);
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + D.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.tables A
INNER JOIN sys.schemas B ON A.schema_id = B.schema_id
INNER JOIN sys.extended_properties C
INNER JOIN sys.key_constraints D ON C.major_id = D.object_id ON A.object_id = D.parent_object_id
WHERE
D.type_desc = N'PRIMARY_KEY_CONSTRAINT';
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + D.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.tables A
INNER JOIN sys.schemas B ON A.schema_id = B.schema_id
INNER JOIN sys.extended_properties C
INNER JOIN sys.key_constraints D ON C.major_id = D.object_id ON A.object_id = D.parent_object_id
WHERE
D.type_desc = N'UNIQUE_CONSTRAINT'
AND
(
C.value <> '1'
AND C.value <> 1
);
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''TABLE'', @level1name = [' + D.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.check_constraints B ON A.major_id = B.object_id
INNER JOIN sys.schemas C
INNER JOIN sys.tables D ON C.schema_id = D.schema_id ON B.parent_object_id = D.object_id;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''INDEX'', @level2name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(B.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(B.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.indexes A
INNER JOIN sys.extended_properties B ON A.object_id = B.major_id AND A.index_id = B.minor_id
INNER JOIN sys.tables C
INNER JOIN sys.schemas D ON C.schema_id = D.schema_id ON A.object_id = C.object_id
WHERE
B.class_desc = N'INDEX'
AND A.is_primary_key = 0;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.foreign_keys B ON A.major_id = B.object_id
INNER JOIN sys.tables C ON B.parent_object_id = C.object_id
INNER JOIN sys.schemas D ON C.schema_id = D.schema_id;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(D.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(D.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.default_constraints A
INNER JOIN sys.schemas B
INNER JOIN sys.tables C ON B.schema_id = C.schema_id ON A.parent_object_id = C.object_id
INNER JOIN sys.extended_properties D ON A.object_id = D.major_id;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''VIEW'', @level1name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.views B ON A.major_id = B.object_id
INNER JOIN sys.schemas C ON B.schema_id = C.schema_id
WHERE
A.minor_id = 0;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''VIEW'', @level1name = [' + C.name + '] , @level2type = ''COLUMN'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.columns B ON A.major_id = B.object_id AND A.minor_id = B.column_id
INNER JOIN sys.views C ON A.major_id = C.object_id
INNER JOIN sys.schemas D ON C.schema_id = D.schema_id
WHERE
A.class = 1
AND
(
A.value <> '1'
AND A.value <> 1
);
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''VIEW'', @level1name = [' + C.name + '] , @level2type = ''INDEX'', @level2name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(B.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(B.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.indexes A
INNER JOIN sys.extended_properties B ON A.object_id = B.major_id AND A.index_id = B.minor_id
INNER JOIN sys.views C
INNER JOIN sys.schemas D ON C.schema_id = D.schema_id ON A.object_id = C.object_id
WHERE
B.class_desc = N'INDEX';
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''FUNCTION'', @level1name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.objects B ON A.major_id = B.object_id
INNER JOIN sys.schemas C ON B.schema_id = C.schema_id
WHERE
B.type_desc LIKE N'%FUNCTION%'
AND A.minor_id = 0;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''PROCEDURE'', @level1name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.procedures B ON A.major_id = B.object_id
INNER JOIN sys.schemas C ON B.schema_id = C.schema_id
WHERE
A.minor_id = 0;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''TRIGGER'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.triggers B ON A.major_id = B.object_id
WHERE
B.parent_class_desc = N'DATABASE';
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] , @level2type = ''TRIGGER'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.tables A
INNER JOIN sys.triggers B ON A.object_id = B.parent_id
INNER JOIN sys.extended_properties C ON B.object_id = C.major_id
INNER JOIN sys.schemas D ON A.schema_id = D.schema_id;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''VIEW'', @level1name = [' + A.name + '] , @level2type = ''TRIGGER'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.views A
INNER JOIN sys.triggers B ON A.object_id = B.parent_id
INNER JOIN sys.extended_properties C ON B.object_id = C.major_id
INNER JOIN sys.schemas D ON A.schema_id = D.schema_id;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''PARTITION FUNCTION'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.partition_functions B ON A.major_id = B.function_id;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''PARTITION SCHEME'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.partition_schemes B ON A.major_id = B.function_id;
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.data_spaces B ON A.major_id = B.data_space_id
WHERE
B.type_desc = 'ROWS_FILEGROUP';
SELECT
'EXEC sys.sp_addextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + C.name + '], @level1type = ''LOGICAL FILE NAME'', @level1name = ' + B.name + ' ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + ''''
FROM
sys.extended_properties A
INNER JOIN sys.database_files B ON A.major_id = B.file_id
INNER JOIN sys.data_spaces C ON B.data_space_id = C.data_space_id
WHERE
A.class_desc = N'DATABASE_FILE';¿Cómo puedo ver esta información?
Y ahora llegamos a la parte más interesante del trabajo de formateo, que es la visualización de los metadatos registrados. Esta visualización puede ser de 2 tipos:
- Documentación oficial: Documento en formato CHM, HTML, Word o PDF, donde se puede visualizar toda la información, como tablas, Procedimientos Almacenados, Vistas,
etc, y sus descripciones - Durante el desarrollo: Herramientas que permiten visualizar la documentación en el propio SSMS, permitiéndote acceder a esta información de forma rápida, a medida que consultas y accedes a los objetos.
Apex SQL completo
Herramienta gratis que tiene como objetivo mejorar (MUCHA) la productividad al escribir consultas de SQL Server en SSMS y es el principal competidor de RedGate SQL Prompt.
Una de las características de esta herramienta es que te permite ver metadatos (Propiedad extendida) de objetos mientras estás programando, como se muestra a continuación:
Mensaje SQL de RedGate
Herramienta comercial que tiene como objetivo mejorar (MUCHA) la productividad al escribir consultas de SQL Server en SSMS y es líder del mercado (con razón) en este segmento. Realmente es un recurso sensacional, en el que incluso escribí un artículo. SQL Server: escriba T-SQL como un Ninja utilizando el indicador SQL de Redgate.
Una de las características de esta herramienta es que te permite ver metadatos (Propiedad extendida) de objetos mientras estás programando, como se muestra a continuación:
RedGate SQLDoc
Utilizando esta herramienta de documentación comercial de RedGate, usted podrá generar documentación completa de sus bases de datos, con información de estadísticas, índices, tablas, procedimientos y una serie de información técnica sobre sus objetos y bases de datos de instancias, además de los metadatos registrados por usted (Propiedad Extendida).
Ejemplos:
datosedo
Usando esta herramienta de documentación comercial (puede usar un gratis, pero con limitaciones), podrás generar documentación completa de tus bases de datos, con información de estadísticas, índices, tablas, procedimientos y una serie de información técnica sobre tus objetos y bases de datos de instancias, además de los metadatos registrados por ti (Propiedad Extendida).
Ejemplos:
¿Quieres más opciones?
- Documento ApexSQL (ejemplos)
- datosedo
- Reportero de esquemas DTM (ejemplos)
- dbdesc (ejemplos)
- DBScribe 1.4 para servidor SQL (ejemplos)
- ¡Documento! incógnita
- DOCxPRENSA
- LiveDoco
- Documento SQL de Red-Gate (demostración)
- esquematodoc (ejemplos)
- SQLDocKit
- Especificaciones SQL (ejemplos)
- Diccionario de datos SQL
- SQLDoc
- Herramienta de documentación SQL (ejemplos)
- Generador de ayuda SQL
- Descodificador de código fuente T-SQL
- tortuga SQL
Conclusión
Como puede ver, hay varias formas de documentar y describir los objetos de su base de datos. Y existen varias herramientas para generar documentación oficial y también para ver esta información en tiempo real, según programas.
Citando como ejemplo un caso real, en una determinada empresa, se encontraba documentación de objetos bancarios, pero en hojas de cálculo Excel compartidas en la red. Esta hoja de cálculo no estaba actualizada, la búsqueda de información era muy pobre y la mayoría de las personas debían buscar constantemente las descripciones de cada columna de esta hoja de cálculo, generando una enorme pérdida de tiempo para los equipos de BI y Desarrollo.
Para mejorar la productividad de estos equipos, el DBA y yo Carolina Goltara, llevamos a cabo un análisis de mercado y elegimos la herramienta Redgate SQL Doc como herramienta de documentación oficial de la empresa.
Importamos, de forma automatizada, todos los textos registrados en las hojas de cálculo a la base de datos (usando los SP mostrados en este post) y el proyecto fue muy exitoso, ya que esto aceleró mucho la productividad de los equipos, ya que allí utilizaron SQL Prompt, cargando los metadatos de la documentación tan pronto como se actualizaron en la base de datos.
Además, la documentación generada en formato HTML se puso a disposición de todos en un servidor web, actualizada y accesible para todos.
Espero que te haya gustado este post y que te pueda ser útil.
Un abrazo y hasta luego.
Comentários (0)
Carregando comentários…