比较来自两个不同数据库的两个不同表格列

Question

我是一名有用的助手，可以为您翻译文本。

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

例如：

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

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

假设在测试数据库中，我已经创建了一个表如下：

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

现在我需要比较两个表的列。

我不想使用Red-Gate工具。

有人可以帮助我吗？

- Franklin

Answer 1

你是不是只是不想使用Red-gate工具，还是基本上不想使用第三方工具？为什么不试用模式使用它们以完成工作，即使您没有购买的预算？

我们一直在使用Apex Diff工具，但市面上还有很多其他工具。

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

了解系统表并以本地方式执行此操作非常好，但太耗时了...

- Phill C

Answer 2

你可以使用EXCEPT或INTERSECT集合运算符来实现。如下所示：

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

这将为您提供：

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

• INTERSECT: 返回由交集操作符左侧和右侧的查询返回的任何不同值。

在您的情况下，由于您想从两个不同的数据库中选择，因此必须使用完全限定的表名。它们必须采用database.schema.object_name的形式。 更新：如果您想比较两个表的列名称而不是数据本身，则必须使用元数据表来以与EXCEPT相同的方式比较列名称。

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

• Test database contains the table:

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

  and another database:

• anotherdatabase database contains the table:

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

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

那么您可以这样做：

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;

这应该给你：

请注意: 我已经连接了以下表格:

• databasename.sys.tables, 以及
• databasename.sys.all_columns

与以下表格一起:

• databasename.systypes

只获取具有相同数据类型的列。如果您没有加入此表，则如果两个列具有相同的名称但不同的数据类型，则它们将相同。

- Mahmoud Gamal

---

嗨，Mahmoud Gamel，谢谢你的回复，请记住，设置运算符始终是按行而不是按列进行操作。我的要求是如果任何一个表具有比另一个表更少的列，则必须识别该列名称并将其添加。我希望您能理解。 - Franklin

@user1694000 - 对不起，我误解了。所以，您想要获取那些不存在于任何列中的列名。对吧？只是列名。 - Mahmoud Gamal

非常感谢Mahmoud Gamal，使用sqldatacompare工具解决了问题。实际上我不想使用第三方工具，但是我必须检查1326个表格，所以逐个通过每个表格需要花费很长时间，这就是为什么我使用了第三方工具。无论如何，非常感谢您的合作。 - Franklin

非常感谢Mahmoud Gamal，使用sqldatacompare工具解决了问题。实际上，我不想使用第三方工具，但是我必须检查1326个表，所以逐个表格检查需要时间，因此我使用了第三方工具。关于我们的问题，我们可以很容易地使用上面的代码识别列，但是我如何知道该列来自哪个表格。即它只显示列详细信息，而不带表名，因此上述结果对我来说根本没有用，因为我如何找到列名属于特定的表格。再次非常感谢您的帮助。 - Franklin

EXCEPT：返回左侧查询中与右侧查询相同的任何不同值。 - Sunil Chavan

Answer 3

要比较列，请在SQL SERVER中使用INFORMATION_SCHEMA.COLUMNS表。

这是示例：

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'

- user2232061

Answer 4

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

它可以智能地原谅（数字、字符串和日期）数据类型差异，将它们缩减为通用格式。输出是一个稀疏的制表符分隔文件，带有.xls扩展名，可在电子表格中使用。

- wwmbes