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

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:

假设在master数据库中我创建了一个表,如:

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?

谁能帮我?

3 个解决方案

#1

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 Diff tool 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...

了解系统表以及如何在本地执行此操作非常棒,但这太耗时了......

#2

You can use the EXCEPT or INTERSECT set 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.

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

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

  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:

例如,假设您有两个数据库:

• Test database contains the table:

  测试数据库包含表:

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

  and another database:

  和另一个数据库:

• anotherdatabase database contains the table:

  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 sal and 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

with the table:

与表:

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

仅获取具有相同数据类型的列。如果您没有加入此表,那么如果两列具有相同的名称但数据类型不同,则它们将是相同的。

#3

To compare columns use INFORMATION_SCHEMA.COLUMNS table in a SQL SERVER.

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

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'