如何查找一个数据库相对于另一个数据库缺少的列、约束和索引？

Question

我有一个基于c#.net的Windows应用程序，它使用Microsoft SQL Server 2008中的数据库。在首次部署到客户端时，我们会创建一个数据库副本并将其与UI应用程序一起部署到客户端的远程服务器上。客户端数据库可以是SQL Server 2005及更高版本。

由于这是一个厚客户端应用程序，客户端数据库与我们最新的数据库不同步，而且很遗憾，没有人记录所有所做的更改。因此，我的挑战如下：

1. 如何比较客户端数据库和我们的数据库，以查找任何缺少的数据库表列？如果有的话？ 2. 如何比较客户端数据库和我们的数据库，以查找任何缺失的主/唯一约束？如果有的话？ 3. 如何比较客户端数据库和我们的数据库，以查找客户端数据库中存在但我们数据库中缺失的索引？如果有的话？

请注意，客户端数据库的大小可能在10-100GB之间，因此我不能计划删除所有客户端表并重新创建它。

- DevCod

---

有一些特定的系统表和视图，您可以查询并发现实际在客户端使用的模式。由您来进行比较。(或者您可以使用更新目标的工具，然后开始采取勤奋的方法来解决这个问题) - Steve

如果您使用的是VS2005+ Premiun或Ultimate版本，那么您就拥有一个免费的本地工具，用于模式和数据比较。https://msdn.microsoft.com/en-us/library/aa833435(v=vs.100).aspx - jean

从现在开始，您应该将数据库纳入源代码控制，并保持更好的开发记录！！！ - Sean Lange

Answer 1

您可以使用 SQL Server 内置的功能之一，即 数据层应用程序 来完成此操作。您无需使用额外的工具。

在 SSMS 中，您可以从数据库中提取数据层应用程序（右键单击 -> 任务 -> 提取数据层应用程序），生成 DACPAC 文件，并将文件复制到客户端服务器上以升级该数据库（或生成更新脚本）。

此外，它还可以与 SQL Server 数据工具 完美集成。

- Jakub Lortz

---

我很喜欢你的回答，它应该非常适合我的需求。我之前不知道这个选项，我会尝试一下并在这里告诉你结果。谢谢。 - DevCod

Answer 2

对于这个任务，你需要一款可以比较SQL数据库的软件。就像有很多比较文本的软件一样，也有很多比较数据库的软件。

个人而言，我使用AdoptSQLDiff，但还有其他很多选择，RedGate也开发了一款，我知道其他的也存在。只需在谷歌上搜索“SQL数据库比较”，你就能找到它们。你可能可以在试用期内完成工作。

这些软件会显示哪些表被添加、删除或更改。它同样适用于视图、索引、触发器、存储过程、用户定义函数和约束。更重要的是，这些工具生成脚本以将修改推送到目标数据库。非常方便，但是看一下生成的脚本，有时会通过删除数据来搞砸它，但很容易修复。

如果需要，还可以比较特定表中的数据。

这是另一个界面的屏幕截图，让您了解它的外观。

- AXMIM

---

他可能已经在使用VS，并且它内置了SQL差异比对工具。 - vittore

比较数据库可能会变得非常复杂，这取决于您使用了多少功能。我很久以前尝试手动完成这项工作，但从未涵盖到所有内容。获取专门设计用于执行此项工作的第三方实用程序完全值得花费；核算价格，并查看在线评论/比较（我们使用Redgate的SQLCompare）。我认为在您的情况下最重要的是它们生成“差异”脚本的能力，以便修改目标数据库以解决差异。 - Philip Kelley

当然，没有第三方工具可以完全解决数据库版本之间的任何微小（或其他）数据差异。这一点，您必须“手动”完成。 - Philip Kelley

Answer 3

您可以使用这个简单的脚本，它可以显示表、视图、索引等之间的差异。

Compalex是一个免费的轻量级脚本，用于比较两个数据库模式。它支持MySQL、MS SQL Server和PostgreSQL。

或者看看这个问题如何比较两个MySQL数据库。这个问题是关于比较两个MySQL模式的，但是一些列出的工具支持MSSQL或有MSSQL版本（例如http://www.liquibase.org/）。

另一个答案哪个是比较两个SQL Server数据库（模式和数据）的最佳工具？

- DLevsha

Answer 4

使用SQLServer Management Studio，您可以尝试选择一个数据库，然后选择任务->生成脚本，并选择适当的选项。

对于您想要比较的两个数据库执行相同的操作。您将获得两个文本文件，可以使用文本文件比较器进行比较。

比较将突出显示数据库结构中的差异。

当然，这不是最好的方法。但它可以作为一个开始。如果两个数据库不太不同，您应该能够处理这些差异。

更好的选择是使用一些数据库比较软件。它们旨在比较数据库结构、约束索引等。我从未使用过任何一个，所以无法提供任何建议。

- Gian Paolo

Answer 5

如果只是一次性的需求，可以使用任何差异工具来比较数据库。VS2010+内置了一个，可以让你比较两个不同文件的模式和数据差异。

如果你想解决开发过程中的问题，有多种选择可以实现数据库版本控制。

• 如果您正在使用EF-使用迁移，那就再好不过了。

• 如果您只使用SQL Server，从未考虑过其他RDBMS，请检查DAC（由Jakub提到的数据层应用程序）

• 否则，请查看更通用的解决方案，其中我建议您查看DB.UP，如果Python代码适合您，请查看Alembic，它允许您使用非常好的Python API编写迁移。

• 如果没有任何方法适合您，请创建当前数据库架构的快照，并开始编写差分脚本，您可以使用自编写的工具或DB.UP。

- vittore

Answer 6

我不确定这是否有帮助，但谁知道呢。

那么，在您的本地环境中是否有恢复服务器数据库的方法？如果答案是肯定的，您可以尝试为每个数据库加入系统视图并进行比较？

我建议像这样做（这是一个快速解决方案，因此请原谅格式和其他常见问题）。

USE [master]
GO

SELECT
    LocalDataBaseTable.name AS TableName,
    LocalDataBaseTableColumns.name AS [Column],
    LocalDataBaseTypes.name AS DataType,
    LocalDataBaseTableColumns.max_length,
    LocalDataBaseTableColumns.[precision]
INTO #tmpLocalInfo
FROM LocalTable.sys.columns as LocalDataBaseTableColumns 
INNER JOIN LocalTable.sys.tables AS LocalDataBaseTable
    ON LocalDataBaseTableColumns.object_id = LocalDataBaseTable.object_id
INNER JOIN LocalTable.sys.types AS LocalDataBaseTypes
    ON LocalDataBaseTypes.user_type_id = LocalDataBaseTableColumns.user_type_id

SELECT 
    ServerDataBaseTable.name AS TableName,
    ServerDataBaseTableColumns.name AS [Column],
    ServerDataBaseTypes.name AS DataType,
    ServerDataBaseTableColumns.max_length,
    ServerDataBaseTableColumns.[precision]
INTO #tmpServerInfo
FROM ServerTable.sys.columns as ServerDataBaseTableColumns 
INNER JOIN ServerTable.sys.tables AS ServerDataBaseTable
    ON ServerDataBaseTableColumns.object_id = ServerDataBaseTable.object_id
INNER JOIN ServerTable.sys.types AS ServerDataBaseTypes
    ON ServerDataBaseTypes.user_type_id = ServerDataBaseTableColumns.user_type_id

SELECT
    #tmpServerInfo.* 
FROM #tmpLocalInfo 
RIGHT OUTER JOIN #tmpServerInfo 
    ON #tmpLocalInfo.TableName = #tmpServerInfo.TableName COLLATE DATABASE_DEFAULT
    AND #tmpLocalInfo.[Column] = #tmpServerInfo.[Column]  COLLATE DATABASE_DEFAULT
WHERE #tmpLocalInfo.[Column] IS NULL

DROP TABLE #tmpLocalInfo
DROP TABLE #tmpServerInfo

这将返回有关本地数据库中缺失列的所有信息。思路是调查 'sys' 视图，并找出是否有适合您的解决方案。

- Mitrucho