4
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?
4
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?
3
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.
3
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.
0
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
Browser other questions tagged sql database sql-server
You are not signed in. Login or sign up in order to post.
https://dbcompare.codeplex.com/
– Reginaldo Rigo