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!
SQL From Hell.com 