Comparando tabelas no SQL Server


            E ai pessoal beleza? Estava meio sumido, mas venho com mais uma dica interessante. Recentemente tive que comparar a estrutura de algumas tabelas para fazer merge entre as mesmas (juntar os dados), para isso precisei comparar a estrutura das mesmas para ver se tinha condição de fazer isso. Tenho um projeto em Integration Services que faz o merge automaticamente para mim, só que ele ainda não compara a estrutura das tabelas, ele parte do principio que as mesmas são iguais. então resolvi construir queries que fizessem o trabalho sujo de comparar a estrutura para mim.
            Existe um utilitário de linha de comando do SQL Server que faz a comparação entre tabelas, muito bom por sinal, mas meio enjoado de ficar utilizando (minha opinião), ele se chama tablediff, em um post futuro vou falar sobre ele.
            Seguinte, no SQL Server existe a possibilidade de verificar a estrutura das tabelas que existem nos bancos de dados por meio de comandos SQL, são metadados das tabelas existentes, que podem ser consultados na sys.[catalogo], neste exemplo vou trabalhar com a sys.objects e com a sys.tables para realizar a operação que desejo.

Para ficar fácil o entendimento do exemplo, vou criar duas tabelas bem simples:

--CRIA TABELA TEMP1 E TEMP2
create table temp1 (id int, nome varchar(50),snome varchar(50), altura float)
create table temp2 (id int, nome varchar(50),snome varchar(50), altura float)

--INSERE DADOS EM TEMP1 E TEMP2
insert into temp1 values(1,'fulano','de tal',1.5)
insert into temp1 values(2,'beltrano','de tal',1.6)
insert into temp1 values(3,'ciclano','de tal',1.7)

insert into temp2 values(1,'fulano','de tal',1.5)
insert into temp2 values(2,'beltrano','de tal',1.6)
insert into temp2 values(3,'ciclano','de tal',1.7)

Dando um select nas mesmas, depois de criadas elas ficarão da seguinte forma:

--SELECT NAS TABELAS TEMP1 E TEMP2
select * from temp1
select * from temp2

Visualizando dados nas tabelas criadas.
Figura 1 - Select nas tabelas

Observe na figura 1, são tabelas pequenas e muito simples, podem ser comparadas rapidamente sem nenhum esforço, mas imagine uma tabela com mais de trinta colunas e diferentes tipos, no meu caso, tenho essa situação, então preciso agilizar meu serviço.

            Primeiro, preciso saber o object_id de cada tabela no banco de dados que estou trabalhando, esse object_id  é a identificação interna da tabela no banco de dados, é um metadado da tabela criada.
Para descobrir os object_id de todas as tabelas de um banco de dados, deve ser usada a seguinte querie:

select * from sys.objects where type = 'u'
           
 Porém, se o banco de dados que estiver sendo utilizado possuir muitas tabelas, vai ficar meio confuso de verificar o object_id do que realmente interessa, então é bom especificar o nome das tabelas desejadas, como as tabelas criadas no exemplo começam com temp, utilizo um like para filtrar:

--TRAZ INFORMAÇÕES MAIS BÁSICAS DAS TABELAS CRIADAS
select * from sys.objects where name like 'temp%'

--TRAZ INFORMAÇÕES MAIS DETALHADAS DAS TABELAS CRIADAS
select * from sys.tables where name like 'temp%'

Visualizando informações sobre as tabelas.
Figura 2 - Informações sobre as tabelas

Observando a figura 2, é possível verificar o object_id das tabelas tanto em sys.objects, quanto em sys.tables, a diferença é a quantidade de metadados que são apresentados em cada select.

            Se fizer o seguinte select nas tabelas, é possível verificar a estrutura das mesmas, por meio dos seus metadados, porém ainda não é o ideal, como disse anteriormente, imagine duas tabelas muito grandes.
  
-- VERIFICANDO NOME, TIPO, TAMANHO E COLLATE DAS TABELAS CRIADAS
select name,system_type_id,max_length,collation_name from sys.columns where object_id = 5575058
select name,system_type_id,max_length,collation_name from sys.columns where object_id = 21575115

Visualizando Dados da estrutura da tabela.
Figura 3 - Dados da estrutura da tabela

Na figura 3 já é possível ver se as tabelas são iguais em sua estrutura, mas como disse anteriormente não é o ideal, então podemos melhorar a querie para o que desejamos.

            Vou verificar se os campos de uma tabela estão contidos dentro da outra, se as duas forem iguais ou todos os campos da tabela de origem estiverem contidos na tabela de destino, o resultado será vazio, se forem diferentes, eles serão apresentados no resultado do select.

--VERIFICA SE NOMES DOS CAMPOS SÃO IGUAIS
select name from sys.columns where object_id in
(
            select object_id from sys.tables where name = 'temp2'
)and name not in
(
            select name from sys.columns where object_id in
            (
                        select object_id from sys.tables where name = 'temp1'
            )
)
Visualizando a comparação dos campos.
Figura 4 - Comparando os campos

A figura 4 demonstra o resultado da comparação entre as duas tabelas, e o resultado é vazio, quer dizer que as duas tabelas possuem os mesmos campos, mas ainda não me diz se a estrutura é a mesma, quer dizer que ainda não é o ideal, então faço da seguinte forma:

--VERIFICA SE OS CAMPOS E TIPOS DOS CAMPOS SÃO IGUAIS ENTRE DUAS TABELAS
select sc1.name,sc1.system_type_id,sc1.max_length,sc1.collation_name from sys.columns sc1
join sys.columns sc2 on
            sc1.name = sc2.name and
            sc1.system_type_id <> sc2.system_type_id
            and sc1.max_length <> sc2.max_length
            and sc1.collation_name <> sc2.collation_name
            and sc2.object_id = 5575058
where sc1.object_id = 21575115

Observe que a querie acima é mais trabalhada, nela já utilizo o object_id que descrevi anteriormente e comparo os tipos de campos das tabelas, verificando se o id do tipo de campo é diferente, se o tamanho é diferente e se o collate é diferente. Caso não exista nenhuma diferença entre as tabelas, nenhum resultado será retornado.

Visualizando a comparação de tipos dos campos da tabela.
Figura 5 - Comparação de tipos dos campos da tabela

Assim é possível verificar se duas tabelas são iguais, seja comparando apenas os nomes dos campos, ou obtendo dados mais detalhados dos tipos de campos das tabelas.
Para mim, essas queries foram muito úteis, espero que possa ajudar a outras pessoas também.




Referências:

Catalog Views (Transact-SQL): http://msdn.microsoft.com/pt-br/library/ms174365.aspx
sys.objects (Transact-SQL): http://msdn.microsoft.com/pt-br/library/ms190324.aspx
sys.tables (Transact-SQL): http://msdn.microsoft.com/pt-br/library/ms187406.aspx
OBJECT_ID (Transact-SQL): http://msdn.microsoft.com/pt-br/library/ms190328.aspx
Metadados: http://pt.wikipedia.org/wiki/Metadados


Comentários

Postar um comentário