SQL Server：比较两个表中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2428271/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: compare columns in two tables
提问by Max Al Farakh
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.
我需要一个查询来帮助我比较两个表中的列。我的意思不是行中的数据，我需要比较列本身以找出我错过了表结构的哪些变化。
回答by Gabriele Petrioli
have a look at Red Gate SQL Compare
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
回答by ScottS
I'd really recommend you use third party comparison tool such as SQL Compare already mentioned above or ApexSQL Diffor 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'
回答by Qcpbraca
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 设置值并运行脚本；)
回答by garik
Realy it is a big script. :)
真的，这是一个大剧本。:)
Use red gate sql compare. They offer you 14-day free trial
使用红门 sql 比较。他们为您提供 14 天免费试用
If you realy need script it can be a text and than you can compare both by using any text comparer.
如果你真的需要脚本，它可以是一个文本，然后你可以使用任何文本比较器来比较两者。
