Queries para dicionário de dados – Step 2

Continuando o post de Dicionário de dados,

Desde versões antigas do SQL Server são encontradas system view que permitem de várias formas mapear as entidades do banco de dados, desde relacionar as colunas de uma determinada tabela até mapear relacionamentos, índices e arquivos de armazenamento do banco de dados.

Primeiramente, como encontrar os relacionamentos no SQL Server 2000 e superiores:

SELECT
  OBJECT_NAME(foreigns.rkeyid) Parent_Table,
  OBJECT_NAME(foreigns.fkeyid) Child_Table,
  OBJECT_NAME(foreigns.constid) Key_Name,
  parent_columns.name Parent_Col,
  child_columns.name Child_Col
FROM sys.sysforeignkeys foreigns
INNER JOIN sys.syscolumns parent_columns
  ON foreigns.fkeyid = parent_columns.id
  AND foreigns.fkey = parent_columns.colid
INNER JOIN sys.syscolumns child_columns
  ON foreigns.rkeyid = child_columns.id
  AND foreigns.rkey = child_columns.colid
ORDER BY Parent_Table, Child_Table

A mesma funcionalidade no SQL Server 2005/2008:

SELECT
  OBJECT_NAME(foreigns_columns.[referenced_object_id]) Parent_Table,
  OBJECT_NAME(foreigns_columns.[parent_object_id]) Child_Table,
  OBJECT_NAME(foreigns_columns.[constraint_object_id]) Key_Name,
  parent_columns.name Parent_Col,
  child_columns.name Child_KeyCol
FROM sys.foreign_key_columns foreigns_columns
INNER JOIN sys.columns parent_columns
  ON parent_columns.[object_id] = foreigns_columns.[parent_object_id]
  AND parent_columns.[column_id] = foreigns_columns.[parent_column_id]
INNER JOIN sys.columns child_columns
  ON child_columns.[object_id] = foreigns_columns.[referenced_object_id]
  AND child_columns.[column_id] = foreigns_columns.[referenced_column_id]
ORDER BY Parent_Table, Child_Table

A mesma funcionalidade sem o uso da função “OBJECT_NAME”:

SELECT
  parent.name Parent_Table,
  child.name Child_Table,
  foreigns.name FKey_Name,
  parent_columns.name Parent_Col,
  child_columns.name Child_Col
FROM sys.tables parent
INNER JOIN sys.foreign_keys foreigns
  ON parent.[object_id] = foreigns.[referenced_object_id]
INNER JOIN sys.tables child
  ON child.[object_id] = foreigns.[parent_object_id]
INNER JOIN sys.foreign_key_columns foreigns_columns
  ON foreigns_columns.[constraint_object_id] = foreigns.[object_id]
INNER JOIN sys.columns parent_columns
  ON parent_columns.[object_id] = foreigns_columns.[parent_object_id]
  AND parent_columns.[column_id] = foreigns_columns.[parent_column_id]
INNER JOIN sys.columns child_columns
  ON child_columns.[object_id] = foreigns_columns.[referenced_object_id]
  AND child_columns.[column_id] = foreigns_columns.[referenced_column_id]
ORDER BY Parent_Table, Child_Table

Agora, para qualquer banco de dados ANSI (MySQL, SQL Server, SQL Lite, PostgreSQL, Oracle), utilizando as system views do INFORMATION_SCHEMA:

SELECT
  KU.TABLE_NAME Parent_Table,
  KU2.TABLE_NAME Child_Table,
  FK.CONSTRAINT_NAME FKey_Name,
  KU.COLUMN_NAME Parent_Col,
  KU2.COLUMN_NAME Child_Col
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KU
  ON KU.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KU2
  ON KU2.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
  AND KU.ORDINAL_POSITION = KU2.ORDINAL_POSITION
Anúncios

Um pensamento sobre “Queries para dicionário de dados – Step 2

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s