SQL 比较来自 2 个不同数据库的 2 个不同表列

Compare 2 different tables columns from 2 different databases

提问by Franklin

I have a requirement to compare different tables' columns from 2 different databases, in order to add columns to the master tables based on the requirement.

我需要比较来自 2 个不同数据库的不同表的列，以便根据要求将列添加到主表。

For example:

例如：

Assume in master database I have created one table like:

假设在主数据库中我创建了一个表，如：

create table test(id int,name varchar(10))

Assume in test database I have created one table like

假设在测试数据库中我创建了一张表

create table testings(id int,name varchar(20), sal int)

now I have to compare 2 table columns

现在我必须比较 2 个表列

I don't want to use red-gate tools.

我不想使用红门工具。

Can anyone help me?

谁能帮我？

回答by Phill C

Is it just red-gate tools you don't want to use or basically any third party tool? Why not, even if you don't have the budget to buy you can still use this in trial mode to get the job done?

它只是您不想使用的红门工具还是基本上任何第三方工具？为什么不呢，即使您没有购买预算，您仍然可以在试用模式下使用它来完成工作？

We've been using Apex Difftool but there are many more out there.

我们一直在使用Apex Diff工具，但还有更多。

With so many tools available you can probably run all one by one in trial mode for months…

有了这么多可用的工具，您可能可以在试用模式下一个接一个地运行数月……

Knowing system tables and how to do this natively is great but it's just too time consuming...

了解系统表以及如何在本机执行此操作很棒，但它太耗时了......

回答by Mahmoud Gamal

You can use the EXCEPTor INTERSECTset operators for this. Like so:

您可以为此使用EXCEPT或INTERSECT设置运算符。像这样：

SELECT id, name FROM master.dbo.test
EXCEPT  -- or INTERSECT
SELECT id, name FROM test.dbo.testings

This will give you:

这会给你：

• EXCEPT:returns any distinct values from the left query that are not also found on the right query.

• INTERSECT:returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

• EXCEPT：返回左侧查询中未在右侧查询中找到的任何不同值。

• INTERSECT：返回由 INTERSECT 操作数左侧和右侧的查询返回的任何不同值。

In your case, since you want to select from two different databases, you have to use a fully qualified table names. They have to be in the form database.schema.object_name.

在您的情况下，由于您想从两个不同的数据库中进行选择，您必须使用完全限定的表名。他们必须在形式database.schema.object_name。

Update:If you want compare the two tables columns' names, not the data itself, you have to work with the metadata tables to compare the columns' names the same way with EXCEPT.

更新：如果您想比较两个表列的名称，而不是数据本身，则必须使用元数据表以与EXCEPT.

For instance, suppose you have two databases:

例如，假设您有两个数据库：

• Testdatabase contains the table:

  create table test(id int, name varchar(10), dep varchar(50));
  

  and another database:

• anotherdatabasedatabase contains the table:

  create table testings(id int,name varchar(20), sal int);
  

• Test数据库包含表：

  create table test(id int, name varchar(10), dep varchar(50));
  

  和另一个数据库：

• anotherdatabase数据库包含表：

  create table testings(id int,name varchar(20), sal int);
  

And you want to compare the two tables' columns and get the tables that don't exist in the other table, in our example you need to get saland dep.

并且您想比较两个表的列并获取另一个表中不存在的表，在我们的示例中，您需要获取sal和dep。

Then you can do this:

然后你可以这样做：

SELECT ColumnName
FROM
(
    SELECT  c.name "ColumnName"
    FROM test.sys.tables t
    INNER JOIN test.sys.all_columns c 
            ON t.object_id = c.object_id
    INNER JOIN test.sys.types ty 
            ON c.system_type_id = ty.system_type_id
    WHERE t.name = 'test'
    EXCEPT
    SELECT  c.name
    FROM anotherdatabase.sys.tables t
    INNER JOIN anotherdatabase.sys.all_columns c 
            ON t.object_id = c.object_id
    INNER JOIN anotherdatabase.sys.types ty 
            ON c.system_type_id = ty.system_type_id
    WHERE t.name = 'testings'
) t1
UNION ALL
SELECT ColumnName
FROM
(
    SELECT  c.name ColumnName
    FROM anotherdatabase.sys.tables t
    INNER JOIN anotherdatabase.sys.all_columns c  
            ON t.object_id = c.object_id
    INNER JOIN anotherdatabase.sys.types ty      
            ON c.system_type_id = ty.system_type_id
    WHERE t.name = 'testings'
    EXCEPT
    SELECT  c.name
    FROM test.sys.tables t
    INNER JOIN test.sys.all_columns c 
            ON t.object_id = c.object_id
    INNER JOIN test.sys.types ty 
            ON c.system_type_id = ty.system_type_id
    WHERE t.name = 'test'
) t2;

This should give you:

这应该给你：

Note that:I joined the tables:

请注意：我加入了表格：

• databasename.sys.tables, and
• databasename.sys.all_columns

• databasename.sys.tables， 和
• databasename.sys.all_columns

with the table:

与表：

• databasename.systypes

• databasename.systypes

to get only those columns that have the same data type. If you didn't joined this table, then if two columns have the same name but different data type, they would be the same.

只获取那些具有相同数据类型的列。如果你没有加入这个表，那么如果两列名称相同但数据类型不同，它们将是相同的。

回答by user2232061

To compare columns use INFORMATION_SCHEMA.COLUMNStable in a SQL SERVER.

要比较列，请使用INFORMATION_SCHEMA.COLUMNSSQL SERVER 中的 table。

This is the exmaple:

这是示例：

select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='your_table_name1'
except
select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='your_table_name2'

回答by wwmbes

This is a GPL Java program I wrote for comparing data in any two tables, with a common key and common columns, across any two heterogeneous databases using JDBC: https://sourceforge.net/projects/metaqa/

这是我编写的 GPL Java 程序，用于使用 JDBC 跨任意两个异构数据库比较任意两个表中的数据，具有公共键和公共列：https: //sourceforge.net/projects/metaqa/

It intelligently forgives (numeric, string and date) data type differences by reducing them to a common format. The output is a sparse tab delimited file with .xls extension for use in a spreadsheet.

它通过将（数字、字符串和日期）数据类型差异简化为通用格式来智能地消除这些差异。输出是一个带有 .xls 扩展名的稀疏制表符分隔文件，用于电子表格。