Olá pessoal!
Tudo bem com vocês ?
Neste post eu gostaria de comentar sobre algo muito importante durante o dia a dia de DBA’s e Desenvolvedores de Query, que é a documentação do banco de dados. Dificilmente eu vejo ambientes onde as colunas ou tabelas possuem uma descrição clara do que se trata esse objeto do banco.
Introdução
Para quem cria consultas o dia inteiro, como analistas de BI, essa informação facilita e muito, o entendimento das queries e a precisão das informações. Para inserir essas informações no banco de dados, vamos utilizar um recurso já bem antigo do SQL Server, mas que poucas pessoas utilizam ou até mesmo conhecem, que é o Extended Property.
Utilizando procedures de sistemas, podemos descrever objetos de banco de dados utilizando suas próprias palavras para facilitar o entendimento de outras pessoas.
Uma vez que essas descrições são inseridas no banco de dados, você pode utilizar ferramentas para visualizar essa informação enquanto você desenvolve suas consultas ou mesmo para gerar documentações completas do banco de dados a partir das descrições do Extended Property.
Diferente de algumas ferramentas de documentação de banco de dados, esse recurso guarda as descrições no próprio banco de dados, fazendo com que o tempo gasto para cadastrar essas descrições não seja perdido caso você pretenda trocar a ferramenta de documentação, fora que essas descrições ficarão salvas em um local seguro e com backups (seu banco tem backup né?!).
Como documentar bases de dados SQL Server
Para a documentação das bases de dados SQL Server, vamos utilizar o recurso chamado Extended Property e as procedures de sistema sp_addextendedproperty, sp_updateextendedproperty e sp_dropextendedproperty.
Para que se possa utilizar essas procedures, o usuário deve estar nas database roles db_owner ou ddl_admin (essa role não permite adicionar descrições para o próprio banco de dados, usuários ou roles) ou ter o privilégio de ALTER/CONTROL nos objetos que ele deseja adicionar as descrições. E é claro, usuários de server roles, como sysadmin, também podem utilizar essas procedures.
Os tipos de objetos que podem ser documentados utilizando Extended Property utilizando essas SP’s de sistema (@level1type) são: AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW e XML SCHEMA COLLECTION.
Os subtipos de objetos que podem ser documentados (@level2type) são: COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER e TRIGGER. Os subtipos (@level2type) dependem do tipo (@level1type) para defini-los, como por exemplo, para documentar uma coluna, onde você deverá definir o @level1type = ‘TABLE’ e @level2type = ‘COLUMN’, referenciando a qual tabela essa coluna faz parte.
Visando facilitar a utilização dessas procedures, vou disponibilizar aqui algumas procedures que vão verificar se o objeto em questão já possui Extended Property e, caso tenha, utiliza a sp_updateextendedproperty ou caso não tenha, utiliza a sp_addextendedproperty.
stpExtendedProperty_Tabela
Visualizar código-fontestpExtendedProperty_Coluna
Visualizar código-fontestpExtendedProperty_Trigger
Visualizar código-fontestpExtendedProperty_View
Visualizar código-fontestpExtendedProperty_Procedure
Visualizar código-fontestpExtendedProperty_Function
Visualizar código-fontestpExtendedProperty_Usuario
Visualizar código-fontestpExtendedProperty_Database
Visualizar código-fontePosso documentar os objetos utilizando uma interface?
Além de permitir que você possa documentar seus objetos de bancos de dados utilizando linha de código, você também pode adicionar metadados Extended Property utilizando a interface do SSMS (SQL Server Management Studio) e é bem simples, embora não permita automatização:
Exemplo de Extended Property numa Tabela
Exemplo de Extended Property numa coluna
Exemplo de um formulário para digitar os metadados e documentar o objeto em questão
Como exportar as documentações já feitas?
Com o script abaixo, você poderá gerar facilmente exportar todas os metadados de Extended Property que você possui no database desejado. Isso é útil para gerar um script e aplicar em outra instância do seu ambiente sem precisar fazer o restore do database para isso.
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 |
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'; |
Como posso visualizar essas informações?
E agora chegamos na parte mais interessante do trabalho de formatação, que é a visualização dos metadados cadastrados. Essa visualização pode ser de 2 tipos:
- Documentação oficial: Documento no formato CHM, HTML, Word ou PDF, onde pode-se visualizar todas as informações, como tabelas, Stored Procedures, Views,
etc, e suas descrições - Durante o desenvolvimento: Ferramentas que possibilitam a visualização da documentação no próprio SSMS, permitindo que você acesse essas informações rapidamente, conforme você vai consultando e acessando os objetos.
Apex SQL Complete
Ferramenta gratuita que tem por objetivo, melhorar (e MUITO) a produtividade na escrita de consultas do SQL Server no SSMS e é o principal concorrente do RedGate SQL Prompt.
Um dos recursos dessa ferramenta, é permitir que você possa visualizar metadados (Extended Property) de objetos enquanto você está programando, conforme demonstrações abaixo:
RedGate SQL Prompt
Ferramenta comercial que tem por objetivo, melhorar (e MUITO) a produtividade na escrita de consultas do SQL Server no SSMS e é líder de mercado (com razão) nesse segmento. Realmente é um recurso sensacional, no qual já até fiz o artigo SQL Server – Escreva T-SQL como um Ninja utilizando o Redgate SQL Prompt.
Um dos recursos dessa ferramenta, é permitir que você possa visualizar metadados (Extended Property) de objetos enquanto você está programando, conforme demonstrações abaixo:
RedGate SQLDoc
Utilizando essa ferramenta comercial de documentação da RedGate, você poderá gerar uma documentação completa das suas bases de dados, com informações de estatísticas, índices, tabelas, procedures e mais uma série de informações técnicas sobre seus objetos e databases da instância, além dos metadados cadastrados por você (Extended Property).
Exemplos:
Dataedo
Utilizando essa ferramenta comercial de documentação (você pode usar uma versão gratuita, mas com limitações), você poderá gerar uma documentação completa das suas bases de dados, com informações de estatísticas, índices, tabelas, procedures e mais uma série de informações técnicas sobre seus objetos e databases da instância, além dos metadados cadastrados por você (Extended Property).
Exemplos:
Quer mais opções?
- ApexSQL Doc (exemplos)
- Dataedo
- DTM Schema Reporter (exemplos)
- dbdesc (exemplos)
- DBScribe 1.4 for SQL Server (exemplos)
- Document! X
- DOCxPRESS
- LiveDoco
- Red-Gate’s SQL Doc (demonstração)
- SchemaToDoc (exemplos)
- SQLDocKit
- SqlSpec (exemplos)
- SQL Data Dictionary
- SQLDoc
- SQL Documentation Tool (exemplos)
- SQL Help Builder
- T-SQL Source Code Unscrambler
- turtle SQL
Conclusão
Como vocês puderam observar, existem diversas formas de documentar e descrever seus objetos de banco de dados. E existem várias ferramentas para gerar documentações oficiais e também para visualizar essas informações em tempo real, enquanto você vai programando.
Citando como exemplo um caso real, em uma determinada empresa, existia uma documentação dos objetos de banco, mas em planilhas do Excel compartilhadas na rede. Essa planilha não era atualizada, a busca das informações era muito ruim e a maioria das pessoas tinha que ficar constantemente pesquisando as descrições de cada coluna nessa planilha, gerando um desperdício de tempo muito grande das equipes de BI e Desenvolvimento.
Para melhorar a produtividade dessas equipes, eu e a DBA Caroline Goltara, fizemos uma análise de mercado e optamos pela ferramenta Redgate SQL Doc como ferramenta oficial de documentação da empresa.
Importamos, de forma automatizada, todos os textos cadastrados nas planilhas para o banco de dados (utilizando as SP’s demonstradas nesse post) e o projeto teve um êxito muito grande, pois isso agilizou muito a produtividade das equipes, já que utilizavam o SQL Prompt lá, carregando os metadados das documentações assim que a mesma era atualizado no banco.
Além disso, foi disponibilizada num servidor Web, a documentação gerada no formato HTML, atualizada e acessível a todos.
Espero que tenham gostado desse post e que ele possa ser útil para vocês.
Um abraço e até mais.
VOCÊ É O CARA! OBRIGADO MEU QUERIDO!
Obrigado, Nissandro! Precisando, estamos às ordens!
Ótimo post Dirceu!
Realmente esta forma de documentação é muito útil, muito bom compartilhar este conhecimento e case de sucesso.
Ei Carol! Que bom ver você por aqui.. rs..
Muito obrigado pelo feedback e pela ajuda durante o projeto que citei no próprio post. Foi graças a você que ele foi um caso de sucesso.
Abraço!
Excelente post, Dirceu! Esse é o tipo de dica que dificilmente achamos em nossas pesquisas! Parabéns pela didática e explanação do assunto! Go, Go!
Obrigado pela visita e pelo feedback, Meirieli 🙂