How to compare the structure of two SQL Server databases?

4

I need to compare the DDL structure of two databases, where one is the production bench and the other is the system approval.

What tools can be used to do this?

    
asked by anonymous 24.05.2016 / 17:49

3 answers

3

You can even make a tool for it yourself; is very simple (and it's even simpler because you have only one type of database server involved).

Just look up the database metadata to list tables and columns, for example:

select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' and TABLE_CATALOG = 'nome_base'

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'nome_tabela'

There are views and system tables to query any info about the banks on the server , triggers , constraints , index ...), take a look at the documentation.

Then you can compare the lists by presenting the differences found as you need them.

    
24.05.2016 / 19:09
3

Would you like to return the structure of the same tables or the data that exists between the tables of one bank or another?

If you need the data there is the option to mount an INNER JOIN:

select TB1.coluna_tb1, TB2.coluna_tb2 from [nome_banco1].[dbo].tabela_banco1 TB1
inner join [nome_banco2].[dbo].tabela_banco2 TB2 on TB1.PK = TB2.PK
where TB1.coluna_tb1 = 'VALOR' and TB2.coluna_tb2 = 'VALOR'

If it is the structure, I believe the answer above has already helped.

    
22.07.2016 / 16:10
0

Not satisfied with previous answers. I place my example here comparing first the existing tables and two the fields of the tables.

SELECT A.NAME, B.NAME  
FROM BANCO1.SYS.tables A
LEFT JOIN BANCO2.SYS.TABLES B ON A.NAME = B.NAME 
GROUP BY A.NAME, B.NAME 
HAVING A.NAME IS NULL 
OR B.NAME IS NULL 

SELECT * FROM ( 
SELECT T.NAME TABELA, C.NAME COLUNA, C.system_type_id, C.max_length, C.precision   
FROM BANCO1.SYS.all_columns C
INNER JOIN BANCO1.SYS.tables T ON T.object_id = C.object_id ) A 

LEFT JOIN ( 
SELECT T.NAME TABELA, C.NAME COLUNA, C.system_type_id, C.max_length, C.precision   
FROM BANCO2.SYS.all_columns C
INNER JOIN BANCO2.SYS.tables T ON T.object_id = C.object_id ) B ON B.TABELA = A.TABELA AND B.COLUNA=A.COLUNA 
WHERE A.system_type_id != B.system_type_id 
OR A.max_length != B.max_length
OR A.precision != B.precision 
    
31.07.2018 / 17:12