How to compare the structure of two SQL Server databases?

Question

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

What tools can be used to do this?

Answer 1

You would like to return the structure of the same tables or the data that exist between the tables of a database or other ?

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 above answer has already helped.

Answer 2

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

Just consult the database metadata in order 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 system views and tables to query any database information on the server (stored procedures, triggers, constraints, index...), take a look at the documentation.

Then you can compare the lists showing the differences found according to your need.

Answer 3

Not satisfied with the previous answers. I put here my example 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