Armazenando metadados em tabelas dos bancos de dados SQL Server usando propriedades estendidas
Quase tudo o que é colocado nestes posts é fruto de experiências ocorridas dentro do meu ambiente de trabalho. Por isso, o assunto de hoje não vai sair deste padrão e pretendo contar como a necessidade de documentar trezentas e quarenta e duas tabelas (342) em um banco de dados em apenas um dia e meio de trabalho me empurrou para buscar uma solução que torne o banco auto documentado e possa, de forma rápida e eficiente, oferecer dados sobre os seus objetos, ainda que em um nível muito básico.
A ideia é poder usar o próprio banco de dados para dar informações sobre os seus objetos e ter um dicionário de dados pelo menos parra as tabelas. Uma das maiores dificuldades que sempre encontrei é saber o que cada uma armazena. Em sistemas que irão ficar em produção durante um longo período e consequentemente passarão por várias alterações e manutenções, é indispensável uma documentação que possa acelerar a compreensão de cada objeto, principalmente as tabelas. Considere ainda sistemas de informação onde várias pessoas trabalham para dar manutenção e onde haja alguma rotatividade de pessoal, neste caso se não houver um mínimo de documentação, a tarefa de explicar o funcionamento do banco se torna muito difícil e extensa.
No SQL Server (a partir da versão 2008), foram introduzidas as propriedades estendidas dos objetos. Estas consistem de um dicionário do tipo chave e valor que podem ser vinculados com elementos como: bancos de dados, esquemas (schemas), tabelas, colunas, tipos de dados, etc. No final do artigo, na seção links, coloco os endereços para acessar a documentação oficial. O que quero mostrar aqui é uma utilização para este recurso de forma a documentar as tabelas dos bancos de dados sem precisar lançar mão de ferramentas externas.
As propriedades estendidas de tabelas podem ser acessadas, visualizadas e alteradas usando a ferramenta SQL Server Management Studio (SSMS). Basta selecionar uma tabela, clicar com o botão direito sobre ela e acessar o item Propriedades. Na janela que se abre, existe um seletor para as propriedades estendidas que exibe uma interface para visualizar e alterar estes elementos.
Uma vantagem das propriedades estendidas é que podem ser manipuladas usando instruções Transact SQL (TSQL) com operações para modificação destes dados e também consultas.
Ao se gerar scripts para exportação do schema de uma tabela, as propriedades estendidas também são incluídas, o que facilita a replicação do banco. Observe o exemplo do script que é gerado para a tabela Person.Persons do banco de dados AdventureWorks2012 da Microsoft.
USE [AdventureWorks2012] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [Production].[Product]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [Name] [dbo].[Name] NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL, [MakeFlag] [dbo].[Flag] NOT NULL, [FinishedGoodsFlag] [dbo].[Flag] NOT NULL, [Color] [nvarchar](15) NULL, [SafetyStockLevel] [smallint] NOT NULL, [ReorderPoint] [smallint] NOT NULL, [StandardCost] [money] NOT NULL, [ListPrice] [money] NOT NULL, [Size] [nvarchar](5) NULL, [SizeUnitMeasureCode] [nchar](3) NULL, [WeightUnitMeasureCode] [nchar](3) NULL, [Weight] [decimal](8, 2) NULL, [DaysToManufacture] [int] NOT NULL, [ProductLine] [nchar](2) NULL, [Class] [nchar](2) NULL, [Style] [nchar](2) NULL, [ProductSubcategoryID] [int] NULL, [ProductModelID] [int] NULL, [SellStartDate] [datetime] NOT NULL, [SellEndDate] [datetime] NULL, [DiscontinuedDate] [datetime] NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] … Omitindo a parte que cria os relacionamentos EXEC sys.sp_addextendedproperty @name=N'MS_Description' , @value=N'Primary key for Product records.' , @level0type=N'SCHEMA' ,@level0name=N'Production' , @level1type=N'TABLE' ,@level1name=N'Product' , @level2type=N'COLUMN' ,@level2name=N'ProductID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description' , @value=N'Name of the product.' , @level0type=N'SCHEMA' , @level0name=N'Production' , @level1type=N'TABLE' , @level1name=N'Product' , @level2type=N'COLUMN' , @level2name=N'Name' GO …
Omiti boa parte do código para não ficar extenso. Neste script, toda linha que inicia com EXEC sys.sp_addextendedproperty corresponde a criação de propriedades estendidas.
O cenário
Para demonstrar a utilização deste recurso vamos considerar duas tabelas abaixo:
- PERSONS: Armazena informações sobre os usuários responsáveis pelos projetos. Estes usuários podem ter vários papeis no sistema.
- PROJECTS: armazena informações sobre os projetos que são controlados pelo banco de dados.
As colunas destas estão demonstradas também abaixo:
O objetivo da tabela PERSONS é armazenar pessoas que são responsáveis por registros de projetos mantidos na tabela PROJECTS. Para documentar o objetivo destas tabelas irei usar a stored procedure do sistema SYS.SP_ADDEXTENDEDPROPERTY. Esta precisa receber os seguintes parâmetros, por ordem:
- Nome para a propriedade estendida. Deve ser único.
- Valor a ser armazenado.
- Nome nível do objeto do banco do nível zero que será identificado, no caso do exemplo, “schema”
- Nome do objeto no caso do exemplo “dbo” pois é a esse schema que a minha tabela é vinculada
- Nome do objeto no banco de dados no nível 1, no caso, uma tabela ou “table”
- Identificador ou o nome do objeto no banco
Desenvolvimento
Para fazer a inclusão dos objetos o script ficou da seguinte forma para a tabela PERSONS:
EXEC sys.sp_addextendedproperty @name=N'Description' ,@value=N'Armazena informações sobre os usuários responsáveis pelos projetos' ,@level0type=N'SCHEMA' ,@level0name=N'dbo' ,@level1type=N'TABLE' ,@level1name=N'persons' GO
Para a tabela PROJECTS o script segue abaixo.
EXEC sys.sp_addextendedproperty @name=N'Description' ,@value=N'Armazena informações sobre os projetos que são controlados pelo banco de dados' ,@level0type=N'SCHEMA' ,@level0name=N'dbo' ,@level1type=N'TABLE' ,@level1name=N'projects' GO
Com isto as propriedades estão incluídas e a partir deste ponto, sempre que forem gerados scripts para estes objetosestes dados serão incluídos.
Supondo então que o objetivo agora seja listar as propriedades estendidas das tabelas deve ser executada a FUNCTION FN_LISTEXTENDEDPROPERTY em conjunto com a instrução SELECT para poder determinar as colunas a serem recuperadas. O script pronto abaixo vai listar o tipo do objeto e o seu nome (no caso do exemplo, tabelas apenas), o nome da propriedade e o seu valor.
select objtype, objname, name, value from fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', null, null, null);
Como foi mencionado anteriormente, não é possível ter duas propriedades com o mesmo nome mas, é possível alterar o seu valor com a SP SYS.SP_UPDATEEXTENDEDPROPERTY que deve ser usada como no script abaixo e é muito parecida com a forma com que é usada a SYS.SP_ADDEXTENDEDPROPERTY que faz a criação da property.
Conclusão
Manter a documentação para um banco de dados é uma tarefa difícil e cansativa. Com as propriedades estendidas é possível facilitar o acesso a alguns dados e simplificar o gerenciamento uma vez que possibilita atualizar a documentação do banco sempre que houver alterações na sua estrutura.
Neste post apenas demonstrei como fazer a documentação para as tabelas entretanto você pode ir além e documentar também as colunas ou pelo menos, as que achar mais importantes, como, por exemplo, as chaves primárias e estrangeiras.
Até a próxima.