Facebook Page – Data’s Light

Pessoal, venho através deste post comunicar a vocês a criação da minha página no Facebook.

Nela pretendo manter informações, posts e vídeos sobre eventos, novidades e novos posts sobre conhecimentos técnicos.

Peço a vocês que deem o Like e Sigam para que assim consigamos continuar aprendendo e trocando informações juntos.

Grande abraço!

Atenciosamente,

Arthur Luz | Data’s Light.

Dicionarizando Bancos de Dados SQL Server

Neste post será possível aprender a construir a Dicionarização de Metadados das tabelas e colunas do SQL Server.


Dentro do SQL Server existe uma tabela denominada sys.extended_properties onde é possível salvar uma descrição sobre os objetos do banco de dados. Através dela, é possível criar uma procedure que nos ajudará a dicionarizar as informações de metadados do database.

A partir dessa tabela, o SQL Server disponibiliza três procedures onde é possível que você adicione, altere ou exclua uma descrição para cada tabela e/ou coluna da base de dados. São elas:

  1. sys.sp_addextendedproperty – procedure de sistema usada para adicionar uma descrição ao Schema, Coluna ou Tabela da base de dados;
  2. sys.sp_updateextendedproperty – procedure de sistema usada para alterar uma descrição ao Schema, Coluna ou Tabela da base de dados;
  3. sys.sp_dropextendedproperty – procedure de sistema usada para deletar uma descrição ao Schema, Coluna ou Tabela da base de dados.

Essas procedures possuem os seguintes parâmetros:

  1. @name – Usado para adição do property_name. Para esse caso usaremos a propriedade MS_Description;
  2. @value – Parâmetro onde é possível adicionar a descrição da tabela ou coluna do banco de dados. Obs – Esse parâmetro não pode ser usado na execução da procedure sys.sp_dropextendedproperty;
  3. @level0type – Parâmetro default. Sempre usa-se o valor ‘SCHEMA’;
  4. @level0name – Usado para passagem do nome do Schema;
  5. @level1type – Parâmetro default. Sempre usa-se o valor ‘TABLE’;
  6. @level1name – Usado para passagem do nome do Tabela;
  7. @level2type – Parâmetro default. Sempre usa-se o valor ‘COLUMN’,
  8. @level2name – Usado para passagem do nome da Coluna.

A algum tempo atrás tive acesso a uma proc (não sei quem a criou) denominada [sp_helpdocs]. Essa procedure consome as informações das tabelas de sistema do SQL Server fornecendo dessa maneira um output com as tabelas, colunas e suas respectivas descrições – salvas na tabela  sys.extended_properties através da procedure sys.sp_addextendedproperty.

De posse desse código, implementei-o para que fosse possível ter acesso, além do nome da tabela ou coluna e sua descrição, algumas outras informações importantes que ajudarão na dicionarização de metadados.


Usarei o código abaixo para criação do banco de dados que será usado para teste e demonstração.

USE master
GO

CREATE DATABASE test_helpdocs
GO

USE test_helpdocs
GO

CREATE TABLE tb_pessoa (
 id_pessoa INT PRIMARY KEY IDENTITY,
 nome_pessoa VARCHAR NOT NULL,
 idade INT NULL,
 dt_cadastro DATETIME
 )
GO

CREATE TABLE tb_telefone (
 id_telefone INT PRIMARY KEY IDENTITY,
 id_pessoa INT NOT NULL,
 nr_telefone VARCHAR (11),
 dt_cadastro DATETIME
 )
GO

CREATE TABLE tb_cargo (
 id_cargo INT PRIMARY KEY IDENTITY,
 id_pessoa INT NOT NULL,
 desc_cargo VARCHAR NOT NULL,
 dt_cadastro DATETIME
 )
GO

ALTER TABLE tb_telefone
ADD CONSTRAINT fk_pessoa_telefone 
FOREIGN KEY (id_pessoa)
REFERENCES tb_pessoa (id_pessoa)
GO

ALTER TABLE tb_cargo
ADD CONSTRAINT fk_pessoa_cargo
FOREIGN KEY (id_pessoa)
REFERENCES tb_pessoa (id_pessoa)
GO

Use o código abaixo para criação da procedure [sp_helpdocs] melhorada:

USE test_helpdocs
GO

CREATE PROCEDURE [dbo].[sp_helpdocs] 
 @istable INT,
 @table VARCHAR (MAX)

AS 

DECLARE @sql VARCHAR(MAX)

-- Identifica se o Parametro @istable é igual a 1. Se sim, mostra as informações relacionadas à tabela recebida no parâmetro @table.
IF @istable = 1
BEGIN

SET @sql = '
SELECT DISTINCT
 s.name AS NomeSchema,
 ''tabela'' AS Tipo,
 t.name AS NomeTabela,
 prop.value AS Descricao
FROM sys.tables AS t
JOIN sys.extended_properties AS prop ON prop.major_id = t.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE prop.minor_id = 0
 AND prop.name = ''MS_Description''
 AND t.NAME = ''' + @table + '''
'

EXECUTE (@sql)

END 

-- Identifica se o parâmetro @istable é igual a 0. Se sim, mostra as informações relacionadas às colunas da tabela recebida no parâmetro @table. 
IF @istable = 0
BEGIN

SET @sql = '
SELECT DISTINCT
 t.name AS ''Tabela'',
 t.object_id AS id_tabela,
 c.name AS ''Coluna'',
 UPPER(type.name) AS ''Tipo de Dado'',
 CASE WHEN type.name IN (''varchar'', ''char'', ''nvarchar'', ''nchar'') THEN CAST(c.max_length AS VARCHAR(4))
 ELSE '''' END AS ''Tamanho do Campo'',
 CASE WHEN type.name IN (''tinyint'', ''smallint'', ''int'', ''bigint'', ''money'', ''decimal'', ''float'') THEN CAST(c.precision AS VARCHAR(4))
 ELSE '''' END AS Precisao,
 ISNULL(c.collation_name,'''') AS Collation,
 c.is_nullable AS ''Permite Nulo'',
 c.is_rowguidcol AS ''Row Guid'',
 c.is_identity As ''Auto Incremento'',
 c.is_computed AS ''Coluna Computada'',
 CASE WHEN kc.name IS NOT NULL THEN 1 ELSE 0 END ''Primary Key'',
 ISNULL(kc.name,'''') AS ''Nome da Primary Key'',
 CASE WHEN o.name IS NOT NULL THEN 1 
 ELSE '''' END ''Foreign Key'',
 CASE WHEN o.name IS NOT NULL THEN o.name 
 ELSE '''' END ''Nome da Foreign Key'',
 ISNULL(OBJECT_NAME(ref.referenced_object_id),'''') ''Tabela de Referencia da FK'',
 ISNULL(cref.name,'''') AS ''Coluna de Referencia da FK'',
 prop.value AS ''Descricao''
FROM sys.tables as t
JOIN sys.columns AS c ON t.object_id = c.object_id
LEFT JOIN sys.index_columns AS ic ON ic.object_id = c.object_id 
 AND ic.column_id = c.column_id
LEFT JOIN sys.key_constraints AS kc ON kc.parent_object_id = ic.object_id
LEFT JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id 
 AND fkc.referenced_object_id = c.object_id
LEFT JOIN sys.foreign_key_columns AS ref ON ref.parent_column_id = c.column_id 
 AND ref.parent_object_id = c.object_id
LEFT JOIN sys.columns AS cref ON cref.column_id = ref.referenced_column_id
 AND cref.object_id = ref.referenced_object_id
LEFT JOIN sys.objects AS o ON o.object_id = ref.constraint_object_id
JOIN sys.types AS type ON type.user_type_id = c.user_type_id
LEFT JOIN sys.extended_properties AS prop ON prop.major_id = t.object_id 
 AND prop.minor_id = c.column_id
 AND prop.name = ''MS_Description''
WHERE t.NAME = ''' + @table + '''
'
EXECUTE (@sql)

END

GO

Após ter criado a proc, execute o código abaixo para criar uma descrição para a tabela [dbo].[tb_pessoa] do banco de dados:

USE test_helpdocs
GO

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Tabela que guarda as informãções de Pessoas.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_pessoa'

GO

Agora, execute o código abaixo:

USE test_helpdocs
GO

EXEC sp_helpdocs 
@istable = 1, -- busca as informações relacionadas à tabela 
@table = 'tb_pessoa'

GO

Você encontrará o seguinte resultado:

01

Agora, você irá adicionar as descrições referentes às colunas da tabela tb_pessoa. Use o código abaixo:

USE test_helpdocs
GO

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Esta coluna é a chave primária da tabela tb_pessoa.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_pessoa',
 @level2type = 'COLUMN',
 @level2name = 'id_pessoa';

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Esta coluna armazena o nome das pessoas da tabela tb_pessoa.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_pessoa',
 @level2type = 'COLUMN',
 @level2name = 'nome_pessoa';

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Esta coluna armazena a idade das pessoas da tabela tb_pessoa.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_pessoa',
 @level2type = 'COLUMN',
 @level2name = 'idade';

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Esta coluna armazena a data de cadastro das pessoas na tabela tb_pessoa.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_pessoa',
 @level2type = 'COLUMN',
 @level2name = 'dt_cadastro';

GO

Observe que os parâmetros @level2type e @level2name só foram adicionados no momento em que o cadastro da descrição é realizado em uma coluna.

Feito isso, execute o código abaixo:

USE test_helpdocs
GO

EXEC sp_helpdocs 
@istable = 0, -- busca as informações relacionadas às colunas da tabela
@table = 'tb_pessoa'

GO

Dessa vez você obterá o seguinte resultado:

Temos todas as informações interessantes relacionadas às colunas da tabela passada como parâmetro incluindo nome da PK e das FKs caso haja alguma, se a coluna é nula, computada, auto increment e ect.

Para demonstração disso, iremos agora adicionar as informações relacionadas à tabela tb_telefone. execute o script abaixo:

USE test_helpdocs
GO

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Tabela que guarda as informãções de telefones das Pessoas.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_telefone'

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Esta coluna é a chave primária da tabela tb_telefone.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_telefone',
 @level2type = 'COLUMN',
 @level2name = 'id_telefone';

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Esta coluna é a chave chave estrangeira da tabela tb_pessoa.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_telefone',
 @level2type = 'COLUMN',
 @level2name = 'id_pessoa';

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Esta coluna é guarda o telefone de cada pessoa da tabela tb_pessoa.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_telefone',
 @level2type = 'COLUMN',
 @level2name = 'nr_telefone';

EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = 'Esta coluna armazena a data de cadastro dos telefones na tabela.' ,
 @level0type = 'SCHEMA', 
 @level0name = 'dbo', 
 @level1type = N'TABLE',
 @level1name = 'tb_telefone',
 @level2type = 'COLUMN',
 @level2name = 'dt_cadastro';

GO

Agora, se você executar a [sp_helpdocs] buscando suas colunas através do parâmetro @istable = 0 e @table = ‘tb_telefone’ será possível observar as informações relacionadas a sua constraint foreign key:


Caso você tenha interesse de guardar as informações em tabelas do banco de dados, faremos isso nesta etapa do post.

Você irá criar o Schema doc e duas tabelas neste schema denominadas doc.tb_inf_tabelasdoc.tb_inf_colunas seguindo o script abaixo:

USE test_helpdocs
GO
 
CREATE SCHEMA doc
GO
 
CREATE TABLE doc.tb_inf_tabelas (
 id_tabela INT NOT NULL,
 nome_schema VARCHAR (100) NOT NULL,
 nome_tabela VARCHAR(100) NOT NULL,
 descricao VARCHAR(MAX),
 data_cadastro DATETIME DEFAULT GETDATE()
 )
GO
 
CREATE TABLE doc.tb_inf_colunas (
 id_coluna INT NOT NULL,
 id_tabela INT NOT NULL,
 nome_coluna VARCHAR(100),
 tipo_dado VARCHAR(50),
 tamanho_campo VARCHAR(10),
 precisao VARCHAR(10),
 collation VARCHAR(100),
 permite_nulo BIT,
 row_guid BIT,
 auto_incremento BIT,
 coluna_computada BIT,
 primary_key BIT,
 nome_primary_key VARCHAR(100),
 foreign_key BIT,
 nome_foreign_key VARCHAR(100),
 tabela_referencia_foreign_key VARCHAR(100),
 coluna_referencia_foreign_key VARCHAR(100),
 descricao VARCHAR(MAX),
 data_cadastro DATETIME DEFAULT GETDATE()
 )
GO

ALTER TABLE doc.tb_inf_tabelas
ADD CONSTRAINT pk_tb_inf_colunas PRIMARY KEY (id_tabela)
GO

ALTER TABLE doc.tb_inf_tabelas 
ADD CONSTRAINT unique_nome_tabela 
UNIQUE (nome_schema, nome_tabela)
GO

ALTER TABLE doc.tb_inf_colunas
ADD CONSTRAINT fk_table 
FOREIGN KEY (id_tabela)
REFERENCES doc.tb_inf_tabelas (id_tabela)
GO

ALTER TABLE doc.tb_inf_colunas 
ADD CONSTRAINT unique_nome_coluna 
UNIQUE (id_tabela, nome_coluna)
GO

Feito isso, criaremos uma nova procedure denominada [sp_addinformation_sp_helpdocs]. Nessa procedure iremos não somente criar as informações de descrição de cada tabela e cada objeto do banco de dados mas também iremos inserir essas informações nas nossas tabelas de documentação do schema doc

Crie a stored procedure [sp_addinformation_sp_helpdocs] com a query abaixo. Após criada, também a execute sem passar parâmetro algum para que seja seja possível a execução de uma carga inicial nas tabelas do schema doc:


USE test_helpdocs
GO

CREATE PROCEDURE sp_addinformation_sp_helpdocs 
 @table VARCHAR(100) = NULL,
 @column VARCHAR(100) = NULL,
 @schema VARCHAR(100) = NULL,
 @description SQL_VARIANT = NULL,
 @istable INT = NULL

AS

---------------------------------------------------------------------------------------------
----------------------------- /* ADD Information SP_HELPDOCS */ -----------------------------
---------------------------------------------------------------------------------------------

-- Verifica se as a tabela doc.tb_inf_tabelas está vazia. Se sim, realiza o insert inicial
IF NOT EXISTS (SELECT 1
 FROM sys.extended_properties prop
 JOIN doc.tb_inf_tabelas doct ON prop.major_id = doct.id_tabela
 WHERE minor_id = 0 
 AND nome_tabela IS NOT NULL)

BEGIN 

INSERT INTO doc.tb_inf_tabelas
SELECT DISTINCT
 t.object_id AS id_tabela,
 s.name AS nome_schema,
 t.name AS Nome,
 CAST(prop.value AS VARCHAR(MAX)) AS Descricao,
 GETDATE()
FROM sys.tables AS t
JOIN sys.extended_properties AS prop ON prop.major_id = t.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE prop.minor_id = 0
 AND prop.name = 'MS_Description'

END

-- Verifica se as a tabela doc.tb_inf_colunas está vazia. Se sim, realiza o insert inicial
IF NOT EXISTS (SELECT 1
 FROM sys.extended_properties prop
 JOIN doc.tb_inf_tabelas doct ON prop.major_id = doct.id_tabela
 JOIN doc.tb_inf_colunas docc ON doct.id_tabela = docc.id_tabela
 WHERE minor_id <> 0 
 AND nome_coluna IS NOT NULL)

BEGIN

INSERT INTO doc.tb_inf_colunas
SELECT DISTINCT
 c.column_id AS id_coluna,
 t.object_id AS id_tabela,
 c.name AS 'Coluna',
 UPPER(type.name) AS 'Tipo de Dado',
 CASE WHEN type.name IN ('varchar', 'char', 'nvarchar', 'nchar') THEN CAST(c.max_length AS VARCHAR(4))
 ELSE '' END AS 'Tamanho do Campo',
 CASE WHEN type.name NOT IN ('varchar', 'char', 'nvarchar', 'nchar') THEN CAST(c.precision AS VARCHAR(4))
 ELSE '' END AS Precisao,
 ISNULL(c.collation_name,'') AS Collation,
 c.is_nullable AS 'Permite Nulo',
 c.is_rowguidcol AS 'Row Guid',
 c.is_identity As 'Auto Incremento',
 c.is_computed AS 'Coluna Computada',
 CASE WHEN kc.name IS NOT NULL THEN 1 ELSE 0 END 'Primary Key',
 ISNULL(kc.name,'') AS 'Nome da Primary Key',
 CASE WHEN o.name IS NOT NULL THEN 1 
 ELSE '' END 'Foreign Key',
 CASE WHEN o.name IS NOT NULL THEN o.name 
 ELSE '' END 'Nome da Foreign Key',
 ISNULL(OBJECT_NAME(ref.referenced_object_id),'') 'Tabela de Referencia da FK',
 ISNULL(cref.name,'') AS 'Coluna de Referencia da FK',
 CAST(prop.value AS VARCHAR(MAX)) AS 'Descricao',
 GETDATE()
FROM sys.tables as t
JOIN sys.columns AS c ON t.object_id = c.object_id
LEFT JOIN sys.index_columns AS ic ON ic.object_id = c.object_id 
 AND ic.column_id = c.column_id
LEFT JOIN sys.key_constraints AS kc ON kc.parent_object_id = ic.object_id
LEFT JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id 
 AND fkc.referenced_object_id = c.object_id
LEFT JOIN sys.foreign_key_columns AS ref ON ref.parent_column_id = c.column_id 
 AND ref.parent_object_id = c.object_id
LEFT JOIN sys.columns AS cref ON cref.column_id = ref.referenced_column_id
 AND cref.object_id = ref.referenced_object_id
LEFT JOIN sys.objects AS o ON o.object_id = ref.constraint_object_id
JOIN sys.types AS type ON type.user_type_id = c.user_type_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
LEFT JOIN sys.extended_properties AS prop ON prop.major_id = t.object_id 
 AND prop.minor_id = c.column_id
 AND prop.name = 'MS_Description'
WHERE s.name <> 'doc' AND prop.value IS NOT NULL

END

-- Verifica se a descrição é sobre uma tabela através do parâmetro @istable = 1
IF @istable = 1 AND @description IS NOT NULL

BEGIN 

-- Verifica se já existe informações na sys.extended_properties sobre a tabela.
-- Caso exista, essas informações sofrerão uma atualização.
 IF EXISTS (SELECT 1
 FROM sys.extended_properties prop
 JOIN sys.tables t ON t.object_id = prop.major_id
 JOIN sys.schemas s ON s.schema_id = t.schema_id
 WHERE prop.minor_id = 0
 AND s.name = @schema
 AND t.name = @table
 )
 BEGIN 
 EXEC sys.sp_updateextendedproperty 
 @name = 'MS_Description',
 @value = @description ,
 @level0type = 'SCHEMA', 
 @level0name = @schema, 
 @level1type = N'TABLE',
 @level1name = @table;

-- Realiza o Update das informações que foram atualizadas na tabela 
-- sys.extended_properties na tabela doc.tb_inf_tabelas 
 UPDATE doc.tb_inf_tabelas 
 SET descricao = CAST(@description AS VARCHAR(MAX))
 WHERE nome_schema = @schema 
 AND nome_tabela = @table
 AND nome_schema = @schema;

 END

-- Verifica se já existe informações na sys.extended_properties sobre a tabela.
-- Caso não exista, essas informações serão adicionadas.
 IF NOT EXISTS (SELECT 1 
 FROM sys.extended_properties prop
 JOIN sys.tables t ON t.object_id = prop.major_id
 JOIN sys.schemas s ON s.schema_id = t.schema_id
 WHERE prop.minor_id = 0
 AND s.name = @schema
 AND t.name = @table
 )
 BEGIN
 EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = @description ,
 @level0type = 'SCHEMA', 
 @level0name = @schema, 
 @level1type = N'TABLE',
 @level1name = @table 

 BEGIN TRY 

-- Realiza o Insert das informações que foram adicionadas na tabela 
-- sys.extended_properties na tabela doc.tb_inf_tabelas 
 INSERT INTO doc.tb_inf_tabelas
 SELECT DISTINCT
 t.object_id AS id_tabela,
 s.name AS nome_schema,
 t.name AS Nome,
 CAST(prop.value AS VARCHAR(MAX)) AS Descricao,
 GETDATE()
 FROM sys.tables AS t
 JOIN sys.extended_properties AS prop ON prop.major_id = t.object_id
 JOIN sys.schemas s ON s.schema_id = t.schema_id
 WHERE prop.minor_id = 0
 AND prop.name = 'MS_Description'
 AND t.NAME = @table
 AND s.name = @schema

 END TRY 

 BEGIN CATCH

 UPDATE doc.tb_inf_tabelas 
 SET descricao = CAST(@description AS VARCHAR(MAX))
 WHERE nome_schema = @schema 
 AND nome_tabela = @table
 AND nome_schema = @schema;

 END CATCH 

 END

END

-- Verifica se a descrição é sobre uma coluna através do parâmetro @istable = 0
IF @istable = 0 AND @description IS NOT NULL

BEGIN 

-- Verifica se já existe informações na sys.extended_properties sobre a coluna.
-- Caso exista, essas informações sofrerão uma atualização.
 IF EXISTS (SELECT *
 FROM sys.tables as t
 LEFT JOIN sys.columns AS c ON t.object_id = c.object_id
 LEFT JOIN sys.extended_properties AS prop ON prop.major_id = t.object_id 
 AND prop.minor_id = c.column_id
 AND prop.name = 'MS_Description'
 WHERE prop.minor_id <> 0
 AND t.name = @table
 AND c.name = @column)
 BEGIN 
 EXEC sys.sp_updateextendedproperty 
 @name = 'MS_Description',
 @value = @description ,
 @level0type = 'SCHEMA', 
 @level0name = @schema, 
 @level1type = N'TABLE',
 @level1name = @table,
 @level2type = 'COLUMN',
 @level2name = @column;

-- Realiza o Update das informações que foram atualizadas na tabela 
-- sys.extended_properties na tabela doc.tb_inf_colunas 
 UPDATE c
 SET c.descricao = CAST(@description AS VARCHAR(MAX))
 FROM doc.tb_inf_colunas c
 JOIN doc.tb_inf_tabelas t ON t.id_tabela = c.id_tabela
 WHERE nome_coluna = @column 
 AND t.nome_tabela = @table;
 END 

-- Verifica se já existe informações na sys.extended_properties sobre a coluna.
-- Caso não exista, essas informações serão adicionadas.
 IF NOT EXISTS (SELECT *
 FROM sys.tables as t
 LEFT JOIN sys.columns AS c ON t.object_id = c.object_id
 LEFT JOIN sys.extended_properties AS prop ON prop.major_id = t.object_id 
 AND prop.minor_id = c.column_id
 AND prop.name = 'MS_Description'
 WHERE prop.minor_id <> 0
 AND t.name = @table
 AND c.name = @column)
 
 BEGIN
 EXEC sys.sp_addextendedproperty 
 @name = 'MS_Description',
 @value = @description ,
 @level0type = 'SCHEMA', 
 @level0name = @schema, 
 @level1type = N'TABLE',
 @level1name = @table,
 @level2type = 'COLUMN',
 @level2name = @column

BEGIN TRY

-- Realiza o Insert das informações que foram adicionadas na tabela 
-- sys.extended_properties na tabela doc.tb_inf_colunas 
 INSERT INTO doc.tb_inf_colunas
 SELECT DISTINCT
 c.column_id AS id_coluna,
 --t.name AS 'Tabela',
 t.object_id AS id_tabela,
 c.name AS 'Coluna',
 UPPER(type.name) AS 'Tipo de Dado',
 CASE WHEN type.name IN ('varchar', 'char', 'nvarchar', 'nchar') THEN CAST(c.max_length AS VARCHAR(4))
 ELSE '' END AS 'Tamanho do Campo',
 CASE WHEN type.name IN ('tinyint', 'smallint', 'int', 'bigint', 'money', 'decimal', 'float') THEN CAST(c.precision AS VARCHAR(4))
 ELSE '' END AS Precisao,
 ISNULL(c.collation_name,'') AS Collation,
 c.is_nullable AS 'Permite Nulo',
 c.is_rowguidcol AS 'Row Guid',
 c.is_identity As 'Auto Incremento',
 c.is_computed AS 'Coluna Computada',
 CASE WHEN kc.name IS NOT NULL THEN 1 ELSE 0 END 'Primary Key',
 ISNULL(kc.name,'') AS 'Nome da Primary Key',
 CASE WHEN o.name IS NOT NULL THEN 1 
 ELSE '' END 'Foreign Key',
 CASE WHEN o.name IS NOT NULL THEN o.name 
 ELSE '' END 'Nome da Foreign Key',
 ISNULL(OBJECT_NAME(ref.referenced_object_id),'') 'Tabela de Referencia da FK',
 ISNULL(cref.name,'') AS 'Coluna de Referencia da FK',
 CAST(prop.value AS VARCHAR(MAX)) AS 'Descricao',
 GETDATE()
 FROM sys.tables as t
 JOIN sys.columns AS c ON t.object_id = c.object_id
 LEFT JOIN sys.index_columns AS ic ON ic.object_id = c.object_id 
 AND ic.column_id = c.column_id
 LEFT JOIN sys.key_constraints AS kc ON kc.parent_object_id = ic.object_id
 LEFT JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id 
 AND fkc.referenced_object_id = c.object_id
 LEFT JOIN sys.foreign_key_columns AS ref ON ref.parent_column_id = c.column_id 
 AND ref.parent_object_id = c.object_id
 LEFT JOIN sys.columns AS cref ON cref.column_id = ref.referenced_column_id
 AND cref.object_id = ref.referenced_object_id
 LEFT JOIN sys.objects AS o ON o.object_id = ref.constraint_object_id
 JOIN sys.types AS type ON type.user_type_id = c.user_type_id
 LEFT JOIN sys.extended_properties AS prop ON prop.major_id = t.object_id 
 AND prop.minor_id = c.column_id
 AND prop.name = 'MS_Description'
 WHERE t.NAME = @table
 AND c.name = @column
END TRY 

BEGIN CATCH

 UPDATE c
 SET c.descricao = CAST(@description AS VARCHAR(MAX))
 FROM doc.tb_inf_colunas c
 JOIN doc.tb_inf_tabelas t ON t.id_tabela = c.id_tabela
 WHERE nome_coluna = @column 
 AND t.nome_tabela = @table;

END CATCH


 END

END

GO

EXEC sp_addinformation_sp_helpdocs 
GO

Agora realizaremos a dicionarização da nossa terceira tabela que foi criada no início do post. A tb_cargo. Para isso, usaremos dessa vez a proc [sp_addinformation_sp_helpdocs]. Segue código abaixo:

USE test_helpdocs
GO

EXEC sp_addinformation_sp_helpdocs 
@schema = 'dbo',
@table = 'tb_cargo',
@description = 'Tabela que guarda as informações de cargo das Pessoas.',
@istable = 1
GO

EXEC sp_addinformation_sp_helpdocs 
@schema = 'dbo',
@table = 'tb_cargo',
@column = 'id_cargo',
@description = 'Esta coluna é a chave primária da tabela tb_cargo.',
@istable = 0
GO

EXEC sp_addinformation_sp_helpdocs 
@schema = 'dbo',
@table = 'tb_cargo',
@column = 'id_pessoa',
@description = 'Esta coluna é a chave chave estrangeira da tabela tb_pessoa.',
@istable = 0
GO

EXEC sp_addinformation_sp_helpdocs 
@schema = 'dbo',
@table = 'tb_cargo',
@column = 'desc_cargo',
@description = 'Esta coluna armazena a descrição do cargo das pessoas da tabela tb_pessoa.',
@istable = 0
GO

EXEC sp_addinformation_sp_helpdocs 
@schema = 'dbo',
@table = 'tb_cargo',
@column = 'dt_cadastro',
@description = 'Esta coluna armazena a data de cadastro dos cargos na tabela.',
@istable = 0
GO

Feito isso, ao executar um SELECT nas tabelas do schema doc será possível observar que ambas já possuem todas as informações da de documentação das tabelas que é possível obter através da proc [sp_helpdocs].


Este é um ótimo caminho para realizar a documentação da sua base de dados SQL Server! 

Espero poder ajudá-los.