如何比较两个数据库的架构？

#1 楼

如果由于连接问题而无法使用众多工具中的一种，并且希望进行“离线”比较，则可以通过右键单击数据库并使用“任务... /生成”，使用SSMS为所有数据库对象生成脚本。脚本”功能，并确保选择为每个对象创建一个文件。

完成两个数据库的操作后，将两组脚本分别放在两个单独的文件夹中的本地计算机上，并使用WinMerge （或类似）比较两者。

#2 楼

在用一种简单的方法来完成同一任务后-看到了两个模型之间的变化之后，我编写了以下SQL脚本，该脚本将比较两个架构以确定新列和已删除列。

set nocount on;
-- Set the two variables newmodel and oldmodel to the appropriate database names and execute the script

declare @newmodel varchar(50), @oldmodel varchar(50);

Set @newmodel = '[NewModel to Compare]';
set @oldmodel = '[OldModel to Compare]';


Declare @Temp table (TABLE_SCHEMA varchar(40), TABLE_NAME varchar(40), COLUMN_NAME varchar(50), ORDINAL_POSITION int, IS_NULLABLE varchar(5), NullChange varchar(5), Comment varchar(50));

Declare @script varchar(5000);


set @script = '
Select nc.TABLE_SCHEMA, nc.TABLE_NAME, nc.COLUMN_NAME, nc.ORDINAL_POSITION, nc.IS_NULLABLE, IIF(nc.IS_NULLABLE <> oc.IS_NULLABLE, ''Yes'', ''No''), 
        IIF(oc.COLUMN_NAME IS NULL, convert(varchar(20), ''ADDED COLUMN''), convert(varchar(20), ''--'')) as Comment
    from {NEW}.INFORMATION_SCHEMA.COLUMNS nc
        LEFT join {OLD}.INFORMATION_SCHEMA.COLUMNS oc 
            on nc.TABLE_NAME = oc.TABLE_NAME and nc.COLUMN_NAME = oc.COLUMN_NAME
UNION ALL
    Select oc.TABLE_SCHEMA, oc.TABLE_NAME, oc.COLUMN_NAME, oc.ORDINAL_POSITION, oc.IS_NULLABLE, ''No'', ''DELETED COLUMN'' as Comment
    from {OLD}.INFORMATION_SCHEMA.COLUMNS oc
    where CONCAT(oc.TABLE_NAME, ''.'', oc.COLUMN_NAME) 
        not in (Select CONCAT(TABLE_NAME, ''.'', COLUMN_NAME) from {NEW}.INFORMATION_SCHEMA.COLUMNS)
';


Set @script = replace(@script, '{OLD}', @oldmodel);
Set @script = replace(@script, '{NEW}', @newmodel);

--print @script

Insert into @Temp
    exec(@script);

Select * from @Temp where Comment <> '--'
order by TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME;
go

#3 楼

另一个选择是使用SQL Server数据工具（SSDT），这是Visual Studio的扩展。您可以将数据库模式提取为.dacpac文件，然后将其与另一个.dacpac文件或现有数据库进行比较。 SSDT包含在SQL Server 2012客户端工具中，可以轻松访问。您可以在MSDN站点上找到有关如何运行比较的完整说明。

#4 楼

如果您需要比较多个数据库文件，则可以编写SQLPackage.exe脚本。

我没有适合您的代码，但是您可以查看SQLPackage.exe文档以获取一些启发。
您将主数据库提取到dacpac文件，然后将dacpac文件与其余数据库进行比较。比较的结果可能是更改的xml报告，也可能是您可以运行以同步数据库的.sql文件。 />
然后

sqlpackage.exe /a:Extract /scs:Server=MyLaptopSQL2014;Database=Test; /tf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac  

您可以看一下本文或这篇文章中的示例代码。

#5 楼

我有完全相同的问题，我相信Microsoft SQL Server Management Studio（SSMS）的解决方案比在这里看到的要简单得多。我有一个使用MS SQL Server Express的生产站点，不久以后还会有几个我不想安装VisualStudio或SSMS以外的其他应用程序的站点。

因此，在SSMS中，右键单击数据库以获取其架构。选择“任务”>“生成脚本...”以打开向导，以脚本化整个数据库（或所需对象）的架构和配置。我保留了除路径/文件名以外的所有默认选项，但是该工具有很多选项。该向导创建了一个SQL，然后通过OneDrive将其复制回PC。然后，我使用Notepad ++将SQL与以相同方式针对SIT数据库生成的文件进行比较。您必须从注释中的日期/时间中筛选出匹配项，但否则，这是两个数据库的很好的比较。

保存！编写此脚本要比实际比较困难得多。

#6 楼

搜索“ SQL Server比较”，您会发现很多工具。我们在工作中使用的是Red Gate SQLCompare。它有14天的试用期。但是，由于您在谈论两种不同的环境，因此除非您的客户端向您发送其数据库的备份，否则我认为这对您不起作用。另一种选择是针对系统表（例如sys.indexes，sys.tables等）编写查询。

#7 楼

也许这个免费脚本https://github.com/dlevsha/compalex可以为您提供帮助。它支持Microsoft SQL Server。


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


您可以在此处尝试演示

http://demo.compalex.net/

#8 楼

最简单的方法是使用为此目的而构建的自动化工具，但是如果您无权访问，则可以从INFORMATION_SCHEMA视图中获取所需的所有基本信息。

使用与生成DDL脚本和进行源比较相比，INFORMATION_SCHEMA中的元数据可能是一个更容易的选择，因为您可以更好地控制数据的呈现方式。您不能真正控制生成的脚本在数据库中呈现对象的顺序。此外，这些脚本包含一堆文本，这些文本可能默认情况下取决于实现，并且当您可能真正需要重点关注的是丢失的表，视图或列，或者可能是列数据类型时，可能会导致很多不匹配的“噪音”或大小不匹配。然后，您可以将结果转储到文件中并使用文本文件比较工具（甚至是MS Word），也可以将结果转储到表中并运行SQL查询以查找不匹配项。

#9 楼

为了包括一个被标记为重复的新问题，我添加了此答案。

我曾经不得不比较两个生产数据库，并发现它们之间的任何架构差异。唯一感兴趣的项目是已添加或删除的表以及已添加，删除或更改的列。我不再拥有我开发的SQL脚本，但是接下来是一般策略。数据库不是SQL Server，但我认为适用相同的策略。

首先，我创建了最能描述为元数据库的数据库。该数据库的用户表包含从生产数据库的系统表复制的数据描述。诸如表名，列名，数据类型和精度之类的东西。还有两个生产数据库中都不存在的项目数据库名称。

接下来，我开发了脚本，这些脚本将生产数据库的系统表中的选择与插入用户表相结合最后，我开发了查询以查找一个数据库中存在但另一个数据库中不存在的表，以及两个数据库中仅在一个数据库中的表中的列，以及之间定义不一致的列这两个数据库。

在大约100个表和600列中，我发现了一些不一致之处，其中一个列在一个数据库中被定义为浮点，而在另一个数据库中被定义为整数。最后一个被证明是天赐之物，因为它发现了困扰多个数据库多年的问题。

有关的系统表建议使用元数据库的模型。查询并不难构建，主要围绕group by进行操作，并且count（数据库名称）=1。

对于您的情况，对于700个生产数据库，您可能希望使前两个步骤更自动化比我只用两个数据库做比较但是想法很相似。

#10 楼

我使用的一个很棒的工具（虽然暂时不会更新）仍然是AdeptSqlDiff

是否同时进行模式比较和数据比较。就像RedGate一样，需要付费，但还需要30天的试用期。而且价格还算合理。

#11 楼

那里有许多第三方工具可以进行模式和数据比较以及同步。您可以使用的两个工具是我和我的团队开发的工具，用于进行模式比较的xSQL Schema Compare和用于在具有同一模式的对象之间进行数据比较的xSQL Data Compare。希望这会有所帮助！

免责声明：我隶属于xSQL

#12 楼

市场上有很多工具可以用来完成工作。我的公司正在使用ApexSQL Diff进行比较和同步，因为它对于Azure是免费的，但是无论使用Devart还是Redgate工具都不会出错。

#13 楼

我是SQL DBDiff的粉丝，SQL DBDiff是一个开源工具，可用于比较SQL Server数据库的两个实例的表，视图，函数，用户等，并在源数据库和目标数据库之间生成更改脚本。 br />

#14 楼

我制作了一个MssqlMerge实用程序，该实用程序可以比较MSSQL数据库的结构和数据。有一个免费版本，可以比较表定义，视图，存储过程和函数。此外，还有一个Pro版本支持更多对象类型，并具有“查询结果差异”功能，您可以在其中运行和比较任何查询结果（包括针对系统视图的查询），以比较一些现成的其他详细信息。

#15 楼

检查一下：

SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
       COLUMN_DEFAULT ,
       DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH ,
       NUMERIC_PRECISION ,
       NUMERIC_PRECISION_RADIX ,
       NUMERIC_SCALE ,
       DATETIME_PRECISION
FROM   INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA in ('dbo','meta')
and table_name in (select name from sys.tables)
order by TABLE_SCHEMA ,       TABLE_NAME ,ORDINAL_POSITION

#16 楼

我使用这个免费（开放源代码）工具：OpenDBDiff

#17 楼

DBDiff是最好的工具，您可以在这里找到它。