Queries para dicionário de dados – Step 1

Olá pessoal,

Uma coisa complicada quando se modela ou dá suporte a um bancos de dados, é entender a sua estrutura, para isso pensa-se em documentá-los com o Diagrama de Modelo de Entidades e Relacionamentos (DER ou MER) e/ou com o Dicionário de Dados (DD).

Na categoria Visio do Blog (at. https://sqlfromhell.wordpress.com/category/visio/ ) existe uma série de post descrevendo como fazer o Diagrama de Modelo de Entidades e Relacionamentos (DER ou MER), e no próprio Visio também pode ser feito o Dicionário de Dados (DD).

Como nem todos utilizam ou têm acesso ao Visio, neste post vou relacionar as queries utilizadas para consultar a relação de Tabelas, Colunas e Tipos de dados existentes no SQL Server 2000 ao 2008, que são úteis para realizar outras atividades, como “geradores de código” e “relatórios dinâmicos”.

Primeiramente, para relacionar as tabelas do SQL Server podemos fazer uso de system views para SQL Server 2005 e superiores:

SELECT * FROM sys.tables

ou para SQL Server 2000 e superiores:

SELECT * FROM sysobjects
WHERE xtype LIKE 'U'

Para relacionar as tabelas e suas respectivas colunas para SQL Server 2005 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna
FROM sys.tables T
INNER JOIN sys.columns C
  ON C.object_id = T.object_id

 ou para SQL Server 2000 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna
FROM sysobjects T
INNER JOIN syscolumns C
  ON T.id = C.id
  AND T.xtype LIKE 'U' -- Verifica se o tipo de objeto é uma tabela

Agora uma query from hell para relacionar as colunas ao seus respectivos tipos para SQL Server 2005 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna,
  TY.name as Tipo,
  C.max_length, -- Tamanho em bytes, para nvarchar normalmente se divide este valor por 2
  C.precision, -- Para tipos numeric e decimal (tamanho)
  C.scale -- Para tipos numeric e decimal (números após a virgula)
FROM sys.columns C
INNER JOIN sys.tables T
  ON T.object_id = C.object_id
INNER JOIN sys.types TY
  ON TY.user_type_id = C.user_type_id
ORDER BY T.name, C.name

ou para SQL Server 2000 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna,
  TY.name as Tipo,
  C.length, -- Tamanho em bytes, para nvarchar normalmente se divide este valor por 2 ou utiliza a 'prec'
  C.prec, -- Para tipos numeric e decimal (tamanho)
  C.scale -- Para tipos numeric e decimal (números após a virgula)
FROM syscolumns C  
INNER JOIN sysobjects T
  ON T.id = C.id
  AND T.xtype LIKE 'U' -- Verifica se o tipo de objeto é uma tabela
INNER JOIN systypes TY
  ON C.usertype = TY.usertype
  AND C.xtype = TY.xtype
ORDER BY T.name, C.name

Agora, para qualquer banco de dados ANSI (MySQL, SQL Server, SQL Lite, PostgreSQL, Oracle), utilizando as system views do INFORMATION_SCHEMA, relacionando as colunas e seus respectivos tipos e tabelas:

SELECT
 TABLE_NAME as Tabela,
 COLUMN_NAME as Coluna,
 DATA_TYPE as Tipo,
 CHARACTER_MAXIMUM_LENGTH, 
 NUMERIC_PRECISION, 
 NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY Tabela, Coluna

ou somente as tabelas:

SELECT
 TABLE_NAME as Tabela
FROM INFORMATION_SCHEMA.TABLES
ORDER BY Tabela

Pessoal até o próximo post!

SQL Server 2008 como fonte de dados no Visio

No último post relacionado à modelagem com Visio, não entrei em detalhes sobre como eu criei uma fonte de dados a partir do SQL Server 2008. Então dedicarei este post para específicar este processo.

Um detalhe interessante é que para iniciar este processo, tive que é necessário realizar o download do SQL Server 2008 Management Studio Express ( http://www.microsoft.com/downloads/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en ) ou instalar qualquer versão do SQL Server 2008 ( http://www.microsoft.com/downloads/details.aspx?familyid=7522A683-4CB2-454E-B908-E805E9BD4E28&displaylang=en ), pois o driver utilizado nas versões 6.5 até 2005 não são compatíveis com a versão atual, mas isso é sinal de maior compatibilidade com as novas funcionalidades.

Engenharia Reversa com o Microsoft Office Visio 2007 Professional

Boa madrugada,

Por motivos de insônia e um email me perguntando “o que é possível fazer modelagem do banco com o Visio 2007?”, percebi que não demonstrei em nenhum momento o Microsoft Office Visio 2007 Professional como ferramenta de modelagem, sendo ele a mais comum ferramenta para esta finalidade  encontrada atualmente, porque para conseguir a versão Microsoft Office Visio for Enterprise Architects é necessário ser um MSDN Subscriber Premium.

Para ser possível realizar uma demonstração válida das funcionalidades do Visio (visto que a parte de modelar o banco de dados não mudou), vou realizar praticamente o mesmo procedimento de engenharia reversa de um banco de dados do post passado, mas utilizando o 2007.

Obs.: Se estiver utilizando o Visio 2007, aproveite as funcionalidades de publicar para XPS e PDF, além dos temas de diagramas, que tornam o Visio 2007 mais “agradável” que o 2003.

A primeira diferença no Visio 2007, já é percebida quando se procura o template para criação do MER, que atualmente situa junto aos templates de UML e desenho de interface no menu “Software e Banco de Dados”.

Modelagem com o Visio – Gerando o MER a partir de um banco de dados

Neste post, vou demonstrar a geração do MER/DER de um banco de dados já existente, ou melhor dizendo, fazer a engenharia reversa de um banco de dados.

Nesta demonstração, estarei utilizando Microsoft Office Visio 2003 e um banco de dados SQL Server 2008. Este processo também pode ser executado perfeitamente utilizando Microsoft Office Visio 2007 e qualquer banco de dados que seja previamente suportado pelo Visio, como Access, Oracle, DB2, ou pelo ODBC do Windows ou OLE DB, como o MySQL e SQL Server 2008.

Modelagem com o Visio – Step 2

Retomando nossa modelagem, vamos iniciar a geração do banco de dados a partir do Visio. Antes que alguém diga que não conseguiu serguir esta etapa, lembro novamente que estou utilizando a versão “for Enterprise Architects” do Microsoft Office Visio 2003 e o SQL Server 2005

Ok, no post anterior criamos nossa MER (modelo de entidade relacional) com as tabelas Cliente e Contato, conforme abaixo:

Para gerar nosso banco, primeiramente,

Modelagem com o Visio – Step 1

Antes que me perguntem “por que ele não usa o ERwin, o Visual Studio ou SQL Server Management Studio para fazer o MER do banco de dados?”, primeiro, como o Visio permite realizar toda a documentação de diagramas e telas de um projeto de Software, assim mantemos a documentação em um único formato, sem sair do ambiente de analise diretamente para o desenvolvimento com o Visual Studio e SSMS ou utilizar um software especialista que dificulte os envolvidos no projeto (cliente, analistas, desenvolvedores…) o acesso ao documento do Modelo/Diagrama de Entidade/Objeto Relacional.

No uso do “Microsoft Office Visio 2003 for Enterprise Architects” para modelar banco de dados, primeiramente escolha na categoria “Database” o templete “Database Model Diagram”, este modelo esta