如何将存储过程添加到版本控制 [英] How to add Stored Procedures to Version Control

问题描述

我们的团队第一次体验到没有对我们的数据库进行版本控制的麻烦.我们如何至少将存储过程添加到版本控制中?我们正在开发的当前系统主要依赖于 SP.

Our team just experienced for the first time the hassle of not having version control for our DB. How can we add stored procedures at the very least to version control? The current system we're developing relies on SPs mainly.

推荐答案

背景:我开发了一个拥有近 2000 个存储过程的系统.

Background: I develop a system that has almost 2000 stored procedures.

我发现的关键是将数据库视为应用程序.你永远不会直接用十六进制编辑器打开一个 EXE 并编辑它.与数据库相同；仅仅因为您可以编辑数据库中的存储过程并不意味着您应该这样做.

The critical thing I have found is to treat the database as an application. You would never open an EXE with a hex editor directly and edit it. The same with a database; just because you can edit the stored procedures from the database does not mean you should.

将源代码管理中的存储过程副本视为当前版本.这是你的源代码.检出、编辑、测试、安装并重新检入.下次必须更改时，请遵循相同的步骤.正如应用程序需要构建和部署过程一样，存储过程也应该如此.

Treat the copy of the stored procedure in source control as the current version. It is your source code. Check it out, edit it, test it, install it, and check it back in. The next time it has to be changed, follow the same procedure. Just as an application requires a build and deploy process, so should the stored procedures.

下面的代码是这个过程的一个很好的存储过程模板.它处理更新 (ALTER) 或新安装 (CREATE) 的两种情况.

The code below is a good stored procedure template for this process. It handles both cases of an update (ALTER) or new install (CREATE).

IF EXISTS(SELECT name
            FROM sysobjects
           WHERE name = 'MyProc' AND type = 'P' AND uid = '1')
   DROP PROCEDURE dbo.MyProc
GO

CREATE PROCEDURE dbo.MyProc
AS

GO

但是，在您控制对存储过程的访问的情况下，以下示例会更好.DROP-CREATE 方法丢失了 GRANT 信息.

However following sample is better in situations where you control access to the stored procedures. The DROP-CREATE method loses GRANT information.

IF NOT EXISTS(SELECT name
            FROM sysobjects
           WHERE name = 'MyProc' AND type = 'P' AND uid = '1')
   CREATE PROCEDURE dbo.MyProc
   AS
   PRINT 'No Op'
GO

ALTER PROCEDURE dbo.MyProc
AS

GO

此外，创建一个完全从源代码控制构建数据库的过程有助于保持对事物的控制.

In addition, creating a process to build the database completely from source control can help in keeping things controlled.

从源代码管理创建一个新数据库.使用 Red Gate SQL Compare 之类的工具来比较两个数据库并识别差异.调和差异.

Create a new database from source control. Use a tool like Red Gate SQL Compare to compare the two databases and identify differences. Reconcile the differences.

更便宜的解决方案是简单地使用 SQL Management Studio 的脚本为"功能并进行文本比较.但是，这种方法对 SSMS 用于格式化提取的 SQL 的确切方法非常敏感.

A cheaper solution is to simply use the "Script As" functionality of SQL Management Studio and do a text compare. However, this method is real sensitive to the exact method SSMS uses to format the extracted SQL.

这篇关于如何将存储过程添加到版本控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！