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?
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?
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.
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.
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