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!