比较跨多个数据库的存储过程 (SQL Server)

Question

SQL Gurus——

我们的架构由多个客户数据库和一个公共代码库组成。当我们部署数据库更改时，必须针对每个数据库运行脚本。

由于部署问题，我们的存储过程有时会彼此不同步。我想创建一个脚本来返回这些不匹配的过程，以确保我们在部署后拥有数据库的同步副本。

是否可以通过让脚本查看两个数据库之间的所有过程来比较两个或多个数据库，并返回不匹配的情况？

其效果是：

DATABASE_1 | DATABASE_2  | MISMATCHED_PROCEDURE | DATABASE_1_MODIFY_DATE | DATABASE_2_MODIFY_DATE
Customer_1 | Customer_2  | sp_get_names         | 1/1/2010               | 1/2/2010
Customer_1 | Customer_2  | sp_add_person        | 1/5/2010               | 1/6/2010

作为奖励，是否可以通过将最新脚本应用到过时脚本来让脚本自动同步数据库？

非常感谢！

Answer 1

有很多工具可以做到这一点。最好的之一是 Red-Gate SQL Compare。另一个非常好的选择是使用 Visual Studio Database Professional 来管理数据库架构。除其他外，它将进行非常好的模式比较。

Answer 2

您可以使用下面的脚本来识别哪些过程（以及稍加修改的其他对象）不同。

要同步数据库，您可能需要尝试 ApexSQL Diff。它类似于 Red Gate 的 SQL Compare。

select S1.name [Db1_Schema], O1.name as [Db1_Object], O1.modify_date,
S2.name [Db1_Schema], O2.name as [Db1_Object], O2.modify_date
from database.sys.all_objects O1
inner join database2.sys.all_objects O2 on O1.name = O2.name
inner join database.sys.syscomments C1 on O1.object_id = C1.id
inner join database2.sys.syscomments C2 on O2.object_id = C2.id
inner join database.sys.schemas S1 on O1.schema_id = S1.schema_id
inner join database2.sys.schemas S2 on O2.schema_id = S2.schema_id
where C1.text <> C2.text and
-- remove the line below if you want to search all objects
O1.type = 'P' 

Answer 3

如果您没有适用于数据库架构师 (Data Dude) 的 SQL Compare 或 Visual Studio 团队系统...可以尝试一下...SQL 2005 及更高版本

select t1.name,t1.modify_date,t2.modify_date
 from Database1.sys.procedures t1
join Database2.sys.procedures t2 on t1.name  = t2.name
and  object_definition(t1.object_id) <>  object_definition(t2.object_id)

Answer 4

使用以下内容：

   SELECT DISTINCT
      o1.name AS Object_Name1,
      o1.type_desc as type_desc1,
      o2.name AS Object_Name2,
      o2.type_desc as type_desc2
   FROM DB1.sys.sql_modules m1
   INNER JOIN DB1.sys.objects o1
   ON m1.object_id = o1.object_id
   FULL OUTER JOIN DB2.sys.sql_modules m2
   INNER JOIN DB2.sys.objects o2
   ON m2.object_id = o2.object_id
   ON o1.name = o2.name
   WHERE isnull(m2.definition,'') <> isnull(m1.definition,'')        

Answer 5

Red Gate 的 Sql Compare 是完美的解决方案。
但是，如果您负担不起费用，那么有一个非常好的免费软件：Star Inix 的 Sql Compare http://www.starinix.com/sqlcompare02.htm

Answer 6

简单的答案，但在所有过程上放置并创建脚本将非常简单且有效。

Answer 7

是的，RedGate 的东西很棒，但这就是我为比较两个不同数据库中的存储过程所做的工作：

1. 将所有存储过程编写到单独的文件中。您可以通过 Microsoft SQL Server Management Studio 向导来完成此操作。
2. 对我比较的其他数据库也做了同样的事情。
3. 启动了 KDiff3，我相信它是免费的。
4. 给它需要浏览的两个目录。
5. 现在，双击看到红色的位置来检查结果，下面的面板会告诉您差异。

完毕！

Answer 8

这是切线相关的，但我写了一些提供两个存储过程的文本之间的百分比匹配统计信息的内容： http://www.sqlservercentral.com/scripts/T-SQL/65787/

Answer 9

如果要比较两个数据库中的所有存储过程以及另一个数据库中不存在的存储过程的输出名称，请使用以下命令。请注意，这不会检查存储过程的定义，仅检查其名称，但也有一种方法可以检查它。

-- Declare 2 variable names to hold the name of the databases
DECLARE @DB1 varchar(50)
SET @DB1 = 'nameOfDb1'
DECLARE @DB2 varchar(50)
SET @DB2 = 'nameOfDb2'
EXEC('SELECT
        t1.name,
        t2.name
      FROM '
        + @DB1 +'.sys.procedures t1
      FULL OUTER JOIN '
    + @DB2 + '.sys.procedures t2
      on t1.name = t2.name
      where t1.object_id IS NULL
      OR t2.object_id IS NULL')

Answer 10

没有足够的声誉来发表评论，所以这里有一个新的答案给你。我使用了 John Moore 提供的代码，但添加了 和 C1.colid = C2.colid 来比较跨越多个条目的过程。这是完整的代码：

select S1.name [Db1_Schema], O1.name as [Db1_Object], O1.modify_date,
S2.name [Db1_Schema], O2.name as [Db1_Object], O2.modify_date
from database.sys.all_objects O1
inner join database2.sys.all_objects O2 on O1.name = O2.name
inner join database.sys.syscomments C1 on O1.object_id = C1.id
inner join database2.sys.syscomments C2 on O2.object_id = C2.id
inner join database.sys.schemas S1 on O1.schema_id = S1.schema_id
inner join database2.sys.schemas S2 on O2.schema_id = S2.schema_id
where C1.text <> C2.text and C1.colid = C2.colid and
-- remove the line below if you want to search all objects
O1.type = 'P' 

Answer 11

下面的Procedure可以找出两个不同数据库中Functions、Procedures、Triggers的差异。将数据库名称作为参数传递。

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE Specific_name = 'SP_SCRIPTDIFF')

DROP PROCEDURE DBO.SP_SCRIPTDIFF

GO

CREATE PROCEDURE [dbo].SP_SCRIPTDIFF

@DBNAME1 SYSNAME, 
@DBNAME2 SYSNAME

AS

/*
DATE      : 2016/07/29
AUTHOR    : SEENI 
OBJECTIVE : TO COMPARE THE FUNCTIONS, PROCEDURES AND TRIGGERS IN TWO DIFFERENT DATABASES, PASS NAME OF DATABASE1, AND DATABASE2 AS INPUTS.
*/

BEGIN

SET NOCOUNT ON 

Exec ('select  DISTINCT O1.name as [ObjectName], O1.modify_date As DateIn_'+@DBNAME1+',  O2.modify_date As DateIn_'+@DBNAME2+',o1.type as Type from '+
@DBNAME1+'.sys.all_objects O1 join '+ @DBNAME2+'.sys.all_objects O2 on O1.name = O2.name and  O1.type = O2.type join '+
@DBNAME1+'.sys.syscomments C1 on O1.object_id = C1.id join '+ @DBNAME2+'.sys.syscomments C2 on O2.object_id = C2.id join '+
@DBNAME1+'.sys.schemas S1 on O1.schema_id = S1.schema_id join '+ @DBNAME2+'.sys.schemas S2 on O2.schema_id = S2.schema_id
where C1.text <> C2.text and c1.colid = c2.colid  and O1.Type in (''FN'',''P'',''T'') And o1.Is_Ms_Shipped = 0  Order by O1.type,ObjectName')

RETURN

END 

GO