Query From Hell? – Comparando a estrutura de tabelas diferentes

Boa noite pessoal,

Finalmente tive tempo para respirar e escrever um artigo neste novo ano. Para este artigo, trago algo que normalmente me deparo quando se trabalha com muitas versões de bancos de dados, que é comparar a estrutura das tabelas entre versões de banco de dados ou até mesmo quando existem tabelas semelhantes dentro de um mesmo banco de dados. Desta forma, preparei algumas consultas que relacionam as colunas de tabelas semelhantes que estejam ou não no mesmo banco de dados, mas o conceito é facilmente aplicado para outros tipos de objetos como tabelas, chaves, índices e relacionamentos.

Para situações mais complexas recomendo a utilização de ferramentas como o Visual Studio Database Edition ou o RedGate SQL Compare e Data Compare. Mas como nem sempre é possível contar com estes recursos por não serem ferramentas gratuitas, sempre é bom ter algumas consultas para situações críticas ou verificações mais urgentes.

1. Colunas de uma tabela

Para identificar as colunas de uma tabela, é possível utilizar tanto INFORMATION_SCHEMA.COLUMNS ou sys.columns ou até mesmo sys.syscolumns, conforme abaixo:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'nome da tabela'
SELECT *
FROM sys.columns
WHERE [object_id] = OBJECT_ID('nome da tabela')
SELECT *
FROM sys.syscolumns
WHERE [id] = OBJECT_ID('nome da tabela')

2. Colunas diferentes entre duas tabelas

Nesta primeira consulta teremos como resultado as colunas diferentes entre duas tabelas, utilizando ‘FULL OUTER JOIN’ (ver artigo), tratando as diferenças no momento da seleção:

USE [AdventureWorks]
GO
SELECT
 ISNULL(A.COLUMN_NAME, B.COLUMN_NAME)
 AS [Tabela]
,CASE WHEN A.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + B.TABLE_NAME
 WHEN B.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + A.TABLE_NAME
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT COLUMN_NAME, TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Address'

) A
FULL OUTER JOIN
(
 SELECT COLUMN_NAME, TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'AddressType'

) B
ON A.COLUMN_NAME = B.COLUMN_NAME
GO

3. Colunas diferentes quando se utiliza schemas

Quando se utiliza a view sys.columns, informe dentro da função OBJECT_ID o nome do schema seguido do nome da tabela. No exemplo, é utilizado o schema ‘Person’:

USE [AdventureWorks]
GO
SELECT
 ISNULL(A.[name], B.[name])
 AS [Coluna]
,CASE WHEN A.[name] IS NULL
 THEN 'SOMENTE EM ' + B.[table]
 WHEN B.[name] IS NULL
  THEN 'SOMENTE EM ' + A.[table]
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT [name]
 ,'[Person].[Address]' as [table]
 FROM sys.columns
 WHERE [object_id] = OBJECT_ID('[Person].[Address]')
) A
FULL JOIN
(
 SELECT [name]
 ,'[Person].[AddressType]' as [table]
 FROM sys.columns
 WHERE [object_id] = OBJECT_ID('[Person].[AddressType]')
) B
ON A.[name] = B.[name]
GO

Quando se utiliza INFORMATION_SCHEMA.COLUMNS, é necessário especificar ‘TABLE_SCHEMA’:

USE [AdventureWorks]
GO
SELECT
 ISNULL(A.COLUMN_NAME, B.COLUMN_NAME)
 AS [Tabela]
,CASE WHEN A.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + B.TABLE_NAME
 WHEN B.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + A.TABLE_NAME
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT COLUMN_NAME
 ,TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Address'
 AND TABLE_SCHEMA = 'Person'
) A
FULL OUTER JOIN
(
 SELECT COLUMN_NAME
 ,TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'AddressType'
 AND TABLE_SCHEMA = 'Person'
) B
ON A.COLUMN_NAME = B.COLUMN_NAME
GO

4. Colunas diferentes de tabelas de bancos de dados diferentes

A consulta para a situação mais comum no meu dia-a-dia, dois bancos de dados de versões diferentes. Observe que nos exemplos que é necessário especificar o banco de dados das views.

Utilizando sys.columns:

USE [master]
GO
SELECT
 ISNULL(A.[name], B.[name])
 AS [Coluna]
,CASE WHEN A.[name] IS NULL
 THEN 'SOMENTE EM ' + B.[table]
 WHEN B.[name] IS NULL
  THEN 'SOMENTE EM ' + A.[table]
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT [name]
 ,'[AdventureWorks2008].[Person].[Address]' as [table]
 FROM [AdventureWorks2008].sys.columns
 WHERE [object_id] = OBJECT_ID('[AdventureWorks2008].[Person].[Address]')
) A
FULL JOIN
(
 SELECT [name]
 ,'[AdventureWorks].[Person].[Address]' as [table]
 FROM [AdventureWorks].sys.columns
 WHERE [object_id] = OBJECT_ID('[AdventureWorks].[Person].[Address]')
) B
ON A.[name] = B.[name]
GO

Utilizando INFORMATION_SCHEMA:

USE [master]
GO
SELECT
 ISNULL(A.COLUMN_NAME, B.COLUMN_NAME)
 AS [Tabela]
,CASE WHEN A.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + B.TABLE_NAME
 WHEN B.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + A.TABLE_NAME
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT COLUMN_NAME
 , TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME
 FROM [AdventureWorks2008].INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Address'
 AND TABLE_SCHEMA = 'Person'
) A
FULL OUTER JOIN
(
 SELECT COLUMN_NAME
 , TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME
 FROM [AdventureWorks].INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Address'
 AND TABLE_SCHEMA = 'Person'
) B
ON A.COLUMN_NAME = B.COLUMN_NAME
GO

Eu tinha preparado algumas consultas mais complexas para comparação e merge de bancos, mas creio que somente com o conceito utilizado das consultas acima, seja possível desenvolver consultas para outros tipos de comparação.

Espero que tenham gostado, até o próximo artigo!

2 pensamentos sobre “Query From Hell? – Comparando a estrutura de tabelas diferentes

  1. Valeu amigo muito bom sao pessoas como vc que fazem agente ver a diferença entrem quem programa com o cerebro e quem programa com o coração

  2. Pingback: Verificar a versão dos dados de uma tabela por CHECKSUM « SQL From Hell.com

Deixe uma resposta

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