在 SQL Server 2008 中区分两个表架构的最简单方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3962324/
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
Easiest Way To Diff Two Table Schemas In SQL Server 2008?
提问by sooprise
I have to do checks between a development and release database and do this manually, which is both slow and not 100% reliable (I only visually inspect the tables).
我必须在开发和发布数据库之间进行检查并手动执行此操作，这既缓慢又不是 100% 可靠（我只目视检查表）。
Is there a quick and easy way to compare table schemas automatically? Maybe even a feature that does this built right into SQL server?
是否有一种快速简便的方法来自动比较表模式？也许甚至是 SQL Server 中内置的功能？
Edit: I'm comparing structure only, thank you for pointing this out.
编辑：我只是比较结构，谢谢你指出这一点。
采纳答案by Tim S. Van Haren
I'm a fan of SQL DBDiff, which is an open source tool you can use to compare tables, views, functions, users, etc. of two instances of SQL Server databases and generate a change script between the source and destination databases.
我是SQL DBDiff的粉丝，它是一个开源工具，可用于比较两个 SQL Server 数据库实例的表、视图、函数、用户等，并在源数据库和目标数据库之间生成更改脚本。
回答by Jeremy Elbourn
There are some commercial products that do this; Visual Studio 2010 Premium Edition can compare schemas.
有一些商业产品可以做到这一点；Visual Studio 2010 Premium Edition 可以比较架构。
Some others:
http://www.red-gate.com/products/SQL_Compare/index.htm
http://www.apexsql.com/sql_tools_diff.aspx
其他一些：
http: //www.red-gate.com/products/SQL_Compare/index.htm
http://www.apexsql.com/sql_tools_diff.aspx
回答by Kuberchaun
Data or structure or both? Try RedGate sql compare or data compare. They both have free trials and are awesome.
数据或结构或两者兼而有之？尝试 RedGate sql 比较或数据比较。他们都有免费试用，而且很棒。
http://www.red-gate.com/products/SQL_Compare/index.htm
http://www.red-gate.com/products/SQL_Compare/index.htm
回答by BD.
I'm a little late to the game...but this script I made has worked well for me. It'll work across linked servers too if needed.
我玩游戏有点晚了……但是我制作的这个脚本对我来说效果很好。如果需要，它也可以跨链接服务器工作。
use master
go
DECLARE @Server1 VARCHAR(100) ='[CARNYSQLTEST1].'; --include a dot at the end
DECLARE @DB1 VARCHAR(100) = '[ZipCrim]';
DECLARE @Table1 VARCHAR(100) = 'IntAction';
DECLARE @Server2 VARCHAR(100) ='[CARNYSQLDEV].'; --include a dot at the end
DECLARE @DB2 VARCHAR(100) = '[ZipCrim]';
DECLARE @Table2 VARCHAR(100) = 'IntAction';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL =
'
SELECT Table1.ServerName,
Table1.DBName,
Table1.SchemaName,
Table1.TableName,
Table1.ColumnName,
Table1.name DataType,
Table1.Length,
Table1.Precision,
Table1.Scale,
Table1.Is_Identity,
Table1.Is_Nullable,
Table2.ServerName,
Table2.DBName,
Table2.SchemaName,
Table2.TableName,
Table2.ColumnName,
Table2.name DataType,
Table2.Length,
Table2.Precision,
Table2.Scale,
Table2.Is_Identity,
Table2.Is_Nullable
FROM
(SELECT ''' + @Server1 + ''' ServerName,
''' + @DB1 + ''' DbName,
SCHEMA_NAME(t.schema_id) SchemaName,
t.Name TableName,
c.Name ColumnName,
st.Name,
c.Max_Length Length,
c.Precision,
c.Scale,
c.Is_Identity,
c.Is_Nullable
FROM ' + @Server1 + @DB1 + '.sys.tables t
INNER JOIN ' + @Server1 + @DB1 + '.sys.columns c ON t.Object_ID = c.Object_ID
INNER JOIN sys.types st ON St.system_type_id = c.System_Type_id AND st.user_type_id = c.user_type_id
WHERE t.Name = ''' + @Table1 + ''') Table1
FULL OUTER JOIN
(SELECT ''' + @Server2 + ''' ServerName,
''' + @DB2 + ''' DbName,
SCHEMA_NAME(t.schema_id) SchemaName,
t.name TableName,
c.name ColumnName,
st.Name,
c.max_length Length,
c.Precision,
c.Scale,
c.Is_Identity,
c.Is_Nullable
FROM ' + @Server2 + @DB2 + '.sys.tables t
INNER JOIN ' + @Server2 + @DB2 + '.sys.columns c ON t.Object_ID = c.Object_ID
INNER JOIN sys.types st ON St.system_type_id = c.System_Type_id AND st.user_type_id = c.user_type_id
WHERE t.Name = ''' + @Table2 + ''') Table2
ON Table1.ColumnName = Table2.ColumnName
ORDER BY CASE WHEN Table1.ColumnName IS NULL THEN 2 ELSE 1 END, Table1.ColumnName
'
EXEC sp_executesql @SQL
回答by pageman
Soo,
苏，
Googled this:
谷歌这个：
对于结构：
另见：
My previous answers' links doesn't work anymore for some reason, so here's another answer from TechNet:
由于某种原因，我以前的答案的链接不再有效，所以这里是TechNet的另一个答案：
DECLARE @Sourcedb sysname
DECLARE @Destdb sysname
DECLARE @Tablename sysname
DECLARE @SQL varchar(max)
SELECT @Sourcedb = '<<SourceDatabaseName>>'
SELECT @Destdb = '<<DestinationDatabaseName>>'
SELECT @Tablename = '<<Tablename>>' -- '%' for all tables
SELECT @SQL = ' SELECT Tablename = ISNULL(Source.tablename,Destination.tablename)
,ColumnName = ISNULL(Source.Columnname,Destination.Columnname)
,Source.Datatype
,Source.Length
,Source.precision
,Destination.Datatype
,Destination.Length
,Destination.precision
,[Column] =
Case
When Source.Columnname IS NULL then ''Column Missing in the Source''
When Destination.Columnname IS NULL then ''Column Missing in the Destination''
ELSE ''''
end
,DataType = CASE WHEN Source.Columnname IS NOT NULL
AND Destination.Columnname IS NOT NULL
AND Source.Datatype <> Destination.Datatype THEN ''Data Type mismatch''
END
,Length = CASE WHEN Source.Columnname IS NOT NULL
AND Destination.Columnname IS NOT NULL
AND Source.Length <> Destination.Length THEN ''Length mismatch''
END
,Precision = CASE WHEN Source.Columnname IS NOT NULL
AND Destination.Columnname IS NOT NULL
AND Source.precision <> Destination.precision THEN ''precision mismatch''
END
,Collation = CASE WHEN Source.Columnname IS NOT NULL
AND Destination.Columnname IS NOT NULL
AND ISNULL(Source.collation_name,'''') <> ISNULL(Destination.collation_name,'''') THEN ''Collation mismatch''
END
FROM
(
SELECT Tablename = so.name
, Columnname = sc.name
, DataType = St.name
, Length = Sc.max_length
, precision = Sc.precision
, collation_name = Sc.collation_name
FROM ' + @Sourcedb + '.SYS.objects So
JOIN ' + @Sourcedb + '.SYS.columns Sc
ON So.object_id = Sc.object_id
JOIN ' + @Sourcedb + '.SYS.types St
ON Sc.system_type_id = St.system_type_id
AND Sc.user_type_id = St.user_type_id
WHERE SO.TYPE =''U''
AND SO.Name like ''' + @Tablename + '''
) Source
FULL OUTER JOIN
(
SELECT Tablename = so.name
, Columnname = sc.name
, DataType = St.name
, Length = Sc.max_length
, precision = Sc.precision
, collation_name = Sc.collation_name
FROM ' + @Destdb + '.SYS.objects So
JOIN ' + @Destdb + '.SYS.columns Sc
ON So.object_id = Sc.object_id
JOIN ' + @Destdb + '.SYS.types St
ON Sc.system_type_id = St.system_type_id
AND Sc.user_type_id = St.user_type_id
WHERE SO.TYPE =''U''
AND SO.Name like ''' + @Tablename + '''
) Destination
ON source.tablename = Destination.Tablename
AND source.Columnname = Destination.Columnname '
EXEC (@Sql)
回答by RedFilter
For a free solution, you can use SQL Server Managements Objectsto output the DDL script for each table, view, index, SP, UDF, etc. Then you can compare, either in code, or using a diff tool like WinMerge.
对于免费的解决方案，您可以使用SQL Server 管理对象为每个表、视图、索引、SP、UDF 等输出 DDL 脚本。然后您可以在代码中进行比较，或者使用WinMerge等差异工具进行比较。
回答by Chris Turner
you can take a look at http://cdttools.com/2011/10/sql-diff-erence/its a low cost alternative, will walk schema between two databases and tell you whats changed. You can then use SQL Mgmt studio to generate "script->As Alter" to build change scripts. (caveat: I wrote it)
你可以看看http://cdttools.com/2011/10/sql-diff-erence/它是一种低成本的替代方案，将在两个数据库之间遍历模式并告诉你发生了什么变化。然后，您可以使用 SQL Mgmt studio 生成“script->As Alter”来构建更改脚本。（注意：我写的）
回答by thejustv
If two tables in same database, you can use this query
如果同一个数据库中有两个表，可以使用这个查询
select c2.table_name,c2.COLUMN_NAME
from [INFORMATION_SCHEMA].[COLUMNS] c2
where table_name='table1'
and c2.COLUMN_NAME not in (select column_name
from [INFORMATION_SCHEMA].[COLUMNS]
where table_name='table1')
回答by mehyaa
Modified a bit of BD.'s query, all credit goes to him. (Changed SCHEMA_NAME(schema_id)to sys.schemas join because SCHEMA_NAME(schema_id)works with current db context which is master, changed sorting and changed column names and added the status column)
修改了一些BD. 的查询，所有功劳都归功于他。（更改SCHEMA_NAME(schema_id)为 sys.schemas join 因为SCHEMA_NAME(schema_id)适用于当前的数据库上下文，即master更改排序和更改列名称并添加状态列）
USE master
GO
DECLARE
@Server1 VARCHAR(100) = 'Server1.', -- don't forget to include a dot at the end
@Server2 VARCHAR(100) = 'Server2.', -- don't forget to include a dot at the end
@DB1 VARCHAR(100) = 'Database1',
@DB2 VARCHAR(100) = 'Database2'
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
SELECT
CASE
WHEN s1.[Column] IS NOT NULL
AND s2.[Column] IS NULL
THEN ''New''
WHEN s1.[Column] IS NULL
AND s2.[Column] IS NOT NULL
THEN ''Deleted''
WHEN s1.[Column] IS NOT NULL
AND s2.[Column] IS NOT NULL
AND (s1.[Type] <> s2.[Type]
OR s1.[Length] <> s2.[Length]
OR s1.[Precision] <> s2.[Precision]
OR s1.Scale <> s2.Scale
OR s1.IsNullable <> s2.IsNullable
OR s1.IsIdentity <> s2.IsIdentity
OR s1.IdentitySeed <> s2.IdentitySeed
OR s1.IdentityIncrement <> s2.IdentityIncrement
OR s1.DefaultValue <> s2.DefaultValue)
THEN ''Changed''
ELSE ''Identical''
END [Status],
s1.[Database],
s1.[Schema],
s1.[Table],
s1.[Column],
s1.[Type],
s1.IsCharType,
s1.[Length],
s1.[Precision],
s1.Scale,
s1.IsNullable,
s1.IsIdentity,
s1.IdentitySeed,
s1.IdentityIncrement,
s1.DefaultValue,
s1.[Order],
s2.[Database],
s2.[Schema],
s2.[Table],
s2.[Column],
s2.[Type],
s2.IsCharType,
s2.[Length],
s2.[Precision],
s2.Scale,
s2.IsNullable,
s2.IsIdentity,
s2.IdentitySeed,
s2.IdentityIncrement,
s2.DefaultValue,
s2.[Order]
FROM (
SELECT
''' + @DB1 + ''' AS [Database],
s.name AS [Schema],
t.name AS [Table],
c.name AS [Column],
tp.name AS [Type],
CASE
WHEN tp.collation_name IS NOT NULL
THEN 1
ELSE 0
END AS IsCharType,
CASE
WHEN c.max_length = -1
THEN ''MAX''
ELSE CAST(c.max_length AS VARCHAR(4))
END AS [Length],
c.[precision],
c.scale,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity,
CAST(ISNULL(ic.seed_value, 0) AS INT) AS IdentitySeed,
CAST(ISNULL(ic.increment_value, 0) AS INT) AS IdentityIncrement,
dc.definition AS DefaultValue,
c.column_id AS [Order]
FROM ' + @Server1 + @DB1 + '.sys.tables t
INNER JOIN ' + @Server1 + @DB1 + '.sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN ' + @Server1 + @DB1 + '.sys.columns c ON c.object_id = t.object_id
INNER JOIN ' + @Server1 + @DB1 + '.sys.types tp ON tp.system_type_id = c.system_type_id
LEFT OUTER JOIN ' + @Server1 + @DB1 + '.sys.identity_columns ic ON ic.object_id = t.object_id AND ic.name = c.name
LEFT OUTER JOIN ' + @Server1 + @DB1 + '.sys.default_constraints dc ON dc.object_id = c.default_object_id
) s1
FULL OUTER JOIN (
SELECT
''' + @DB2 + ''' AS [Database],
s.name AS [Schema],
t.name AS [Table],
c.name AS [Column],
tp.name AS [Type],
CASE
WHEN tp.collation_name IS NOT NULL
THEN 1
ELSE 0
END AS IsCharType,
CASE
WHEN c.max_length = -1
THEN ''MAX''
ELSE CAST(c.max_length AS VARCHAR(4))
END AS [Length],
c.[precision],
c.scale,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity,
CAST(ISNULL(ic.seed_value, 0) AS INT) AS IdentitySeed,
CAST(ISNULL(ic.increment_value, 0) AS INT) AS IdentityIncrement,
dc.definition AS DefaultValue,
c.column_id AS [Order]
FROM ' + @Server2 + @DB2 + '.sys.tables t
INNER JOIN ' + @Server2 + @DB2 + '.sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN ' + @Server2 + @DB2 + '.sys.columns c ON c.object_id = t.object_id
INNER JOIN ' + @Server2 + @DB2 + '.sys.types tp ON tp.system_type_id = c.system_type_id
LEFT OUTER JOIN ' + @Server2 + @DB2 + '.sys.identity_columns ic ON ic.object_id = t.object_id AND ic.name = c.name
LEFT OUTER JOIN ' + @Server2 + @DB2 + '.sys.default_constraints dc ON dc.object_id = c.default_object_id
) s2
ON s2.[Schema] = s1.[Schema]
AND s2.[Table] = s1.[Table]
AND s2.[Column] = s1.[Column]
ORDER BY
CASE WHEN s1.[Database] IS NULL THEN s2.[Database] ELSE s1.[Database] END,
CASE WHEN s1.[Schema] IS NULL THEN s2.[Schema] ELSE s1.[Schema] END,
CASE WHEN s1.[Table] IS NULL THEN s2.[Table] ELSE s1.[Table] END,
CASE WHEN s1.[Order] IS NULL THEN s2.[Order] ELSE s1.[Order] END
'
EXEC sp_executesql @SQL
回答by Michael Levy
You can use the SQL Management studio tools to "Generate scripts" from both databases. Then use your favorite text comparison tool to see any differences.
您可以使用 SQL Management Studio 工具从两个数据库“生成脚本”。然后使用您最喜欢的文本比较工具来查看任何差异。
In the old days, this worked great, but in SQL 2005 the generate script code changed and the objects were no longer created in the same order, so the text comparison is less useful. I have not tested this in more recent versions of SQL so it may have been fixed. You can also try http://exportsqlscript.codeplex.com/which I have used with good success to output the DDL as scripts for source code control and comparing versions.
在过去，这很有效，但在 SQL 2005 中，生成脚本代码发生了变化，对象不再以相同的顺序创建，因此文本比较不太有用。我没有在更新的 SQL 版本中测试过这个，所以它可能已经修复。您还可以尝试http://exportsqlscript.codeplex.com/，我已经成功地使用它将 DDL 输出为用于源代码控制和比较版本的脚本。
References:
参考：
