Como aplicar as propriedades estendidas no SQL Server

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:

  1. PERSONS: Armazena informações sobre os usuários responsáveis pelos projetos. Estes usuários podem ter vários papeis no sistema.
  2. 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:

  1. Nome para a propriedade estendida. Deve ser único.
  2. Valor a ser armazenado.
  3. Nome nível do objeto do banco do nível zero que será identificado, no caso do exemplo, “schema”
  4. Nome do objeto no caso do exemplo “dbo” pois é a esse schema que a minha tabela é vinculada
  5. Nome do objeto no banco de dados no nível 1, no caso, uma tabela ou “table”
  6. 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.

Links relacionados