SQL Server:比较两个表中的列

I've recently done a migration from a really old version of some application to the current version and i faced some problems while migrating databases.

最近，我从一个旧版本的应用程序迁移到当前版本，在迁移数据库时遇到了一些问题。

I need a query that could help me to compare columns in two tables. I mean not the data in rows, I need to compare the columns itself to figure out, what changes in table structure I've missed.

我需要一个查询来帮助我比较两个表中的列。我的意思是，不是以行为单位的数据，我需要比较列本身，找出我漏掉了什么表结构的变化。

4 个解决方案

#1

have a look at Red Gate SQL Compare

查看一下Red Gate SQL比较

Otherwise here is a start (for sql server)

否则，这里有一个开始(对于sql server)

select 
 so.name as [table],
 sc.name as [column],
 sc.type, sc.length, sc.prec, sc.scale, sc.collation
from 
 sysobjects so
 inner join syscolumns sc ON so.id = sc.id

where so.type='u'

order by so.name, sc.colorder

you can have a look at the

你可以看一下

 - INFORMATION_SCHEMA.TABLES
 - INFORMATION_SCHEMA.COLUMNS
 - INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 - INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 - INFORMATION_SCHEMA.KEY_COLUMN_USAGE

tables if you want to go deeper..

如果你想走得更远，那就走吧。

[update]

(更新)

Using the INFORMATION_SCHEMA tables

使用INFORMATION_SCHEMA表

SELECT
 [table].TABLE_NAME AS [Table_Name],
 [column].COLUMN_NAME AS [Column_Name],
 COLUMNPROPERTY(object_id([table].[TABLE_NAME]), [column].[COLUMN_NAME], 'IsIdentity') AS [identity],
 [column].DATA_TYPE AS [datatype],
 [column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],
 [column].NUMERIC_PRECISION AS Numeric_precision,
 [column].ORDINAL_POSITION AS [order],
 [column].COLUMN_DEFAULT AS [defaultvalue],
 [column].IS_NULLABLE AS [nullable]
FROM 
 INFORMATION_SCHEMA.TABLES [table] INNER JOIN 
 INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME
WHERE
 [table].TABLE_TYPE = 'BASE TABLE'
 AND [table].TABLE_NAME <> 'sysdiagrams'
ORDER BY 
 [table].TABLE_NAME ASC, 
 [column].ORDINAL_POSITION ASC

#2

I’d really recommend you use third party comparison tool such as SQL Compare already mentioned above or ApexSQL Diff or basically any other tool on the market.

我建议您使用第三方比较工具，如上面已经提到的SQL Compare, ApexSQL Diff或市场上的其他工具。

Even though these are commercial tools you can get a free trial and get the job done if you don’t really need to do this daily.

即使这些是商业工具，如果你不需要每天做这些，你也可以免费试用并完成工作。

If you really need to use SQL for this you can try really simple query like this and then build on top of this.

如果你真的需要使用SQL，你可以尝试像这样简单的查询，然后在这个基础上构建。

select T.name, C.*
from sys.tables T
inner join sys.columns C on T.object_id = C.object_id
where T.name = 'table_name'

#3

Realy it is a big script. :)

真的，这是一个大剧本。:)

Use red gate sql compare. They offer you 14-day free trial

使用red gate sql比较。他们提供14天免费试用

If you realy need script it can be a text and than you can compare both by using any text comparer.

如果您需要脚本，它可以是一个文本，您可以使用任何文本比较器来比较两者。

#4

This works for me (had the same problem and just compiled my solution)

这对我来说是可行的(也有同样的问题，只是编译了我的解决方案)

DECLARE @TableOne VARCHAR(2048) = '',
        @TableTwo VARCHAR(2048) = ''

-- In TableOne but not in TableTwo
SELECT DISTINCT
       @TableOne AS [First table],
       '>>' AS Dir, --Direction
       @TableTwo AS [Second table],
       a.COLUMN_NAME,
       a.DATA_TYPE         
  FROM INFORMATION_SCHEMA.COLUMNS a
 WHERE a.COLUMN_NAME NOT IN (SELECT COLUMN_NAME
                               FROM INFORMATION_SCHEMA.COLUMNS b
                              WHERE b.TABLE_NAME = @TableTwo)
   AND a.TABLE_NAME = @TableOne
UNION ALL
-- In TableTwo but not in TableOne
SELECT DISTINCT
       @TableOne AS [First table],
       '<<' AS Dir, --Direction
       @TableTwo AS [Second table],
       a.COLUMN_NAME,
       a.DATA_TYPE         
  FROM INFORMATION_SCHEMA.COLUMNS a
 WHERE a.COLUMN_NAME NOT IN (SELECT COLUMN_NAME
                               FROM INFORMATION_SCHEMA.COLUMNS b
                              WHERE b.TABLE_NAME = @TableOne)
   AND a.TABLE_NAME = @TableTwo
 ORDER BY Dir DESC, COLUMN_NAME ASC

just set values for @TableOne and @TableTwo and run the script ;)

只需为@TableOne和@TableTwo设置值并运行脚本;)

#1