Pular para o conteúdo principal

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

Postagens mais visitadas deste blog

Como gerar scripts para exportar dados no SQL Server 2008

Uma das tarefas mais comuns no trabalho com desenvolvimento de software que consome dados em bancos como o SQL Server 2008 é a necessidade de em algum momento precisarmos exportar os dados de um banco para outro. Quer seja para realizar testes ou fazer simulações existem várias maneiras de se fazer isto. Neste post eu quero demonstrar um recurso do SQL Server Management Studio (SSMS) que permite realizar esta tarefa rapidamente.Para os que estão acostumados a usar esta ferramenta, já devem saber que é possível gerar scripts para o schema e também transferir os dados entre dois bancos distintos. Isto pode ser feito se o SSMS puder conectar-se com as duas bases, de origem e destino. No exemplo que vou dar, o objetivo é gerar o script apenas para uma tabela do banco de dados de exemplo da Microsoft – Northwind.1. Iniciando o assistenteO assistente deve ser iniciado clicando com o botão direito do mouse sobre o banco onde se encontra a tabela a qual iremos gerar o script. Deve se clicar n…

Pivot dinâmico com SQL Server

Passo a passo para usar pivoteamento dinâmicoOs bancos de dados bem configurados e definidos armazenam os dados de forma a otimizar o acesso, evitando duplicidade e garantindo a integridade. Porém, em muitas situações isto pode dificultar a apresentação de forma adequada sendo necessário preparar os dados usando vários recursos entre os quais, fazer o pivoteamento.Se você não precisou ainda usar ou não sabe o que é consiste em transformar cada linha de uma determinada coluna em colunas de uma nova consulta.Assim, considere uma tabela que armazene as notas bimestrais de um boletim. Uma possível estrutura para esta tabela seria algo assim:ColunaTipo de dado/TamanhoDisciplinanvarchar(50)BimestreIntNotanumeric(5,2)Uma consulta select nesta tabela com alguns dados traria um resultado parecido com o abaixo:Porém pode ser que para apresentar estes dados em um relatório seja necessário transformar cada bimestre em uma coluna e agrupar as notas nestas colunas para que fique dessa forma:Isto po…

Desabilitando o auto commit no SSMS (SQL Server Management Studio)

(Ou, como prevenir desastres e manter o emprego a salvo…)Neste post vai uma pequena mas tremendamente útil dica para desabilitar o auto commit da aplicação SQL Server Management Studio (SSMS) que é usada por dez entre dez usuários do banco de dados SQL Server para fazer consultas, alterações e executar scripts no banco de dados. (Preferências à parte, realmente muita gente usa),A primeira e mais importante notícia é que, diferentemente da ferramenta do Oracle, este editor de scripts do SQL Server vem com o recurso de auto commit ativado por padrão, assim, qualquer instrução DML (alteração dos dados com update, insert e delete) ou DDL (alteração no banco como create, drop, alter, etc.) será imediatamente enviada ao banco e persistida.Isto pode ser altamente crítico pois se estiver executando as instruções em um banco de dados de produção não haverá muitas formas de desfazer se é que haverá.Inicialmente, pode se evitar muitos acidades executando estas instruções dentro de um bloco BEGIN…