如何将存储过程添加到版本控制

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.

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

6 个解决方案

#1

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

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

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.

我发现的关键是将数据库视为一个应用程序。您永远不会直接使用十六进制编辑器打开EXE并进行编辑。与数据库相同;只是因为你可以从数据库编辑存储过程并不意味着你应该。

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.

将源代码管理中的存储过程的副本视为当前版本。这是你的源代码。检查，编辑，测试，安装，然后重新检查。下次必须更改时，请按照相同的步骤进行操作。正如应用程序需要构建和部署过程一样，存储过程也应如此。

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

下面的代码是此过程的良好存储过程模板。它处理更新（ALTER）或新安装（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

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

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

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.

此外，创建一个完全从源代码控制构建数据库的过程可以帮助控制事物。

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.

从源代码管理创建新数据库。使用Red Gate SQL Compare等工具比较两个数据库并识别差异。调和差异。

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.

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

#2

I’d definitely recommend some third party tool that integrates into SSMS. Apart from SQL Source Control mentioned above you can also try SQL Version from Apex.

我肯定会推荐一些集成到SSMS中的第三方工具。除了上面提到的SQL Source Control，您还可以尝试Apex的SQL版本。

Important thing is to make this really easy for developers if you want them to use it and the best way is to use tool that integrates into SSMS.

重要的是，如果您希望开发人员使用它，那么这对开发人员来说非常容易，最好的方法是使用集成到SSMS中的工具。

#3

I think it's good to have each stored procedure scripted to a separate .sql file and then just commit those files into source control. Any time a sproc is changed, update the creation script - this gives you full version history on a sproc by sproc basis.

我认为将每个存储过程编写为单独的.sql文件然后将这些文件提交到源代码控制中是件好事。每次更改sproc时，都要更新创建脚本 - 这将以sproc为基础为您提供完整的版本历史记录。

There are SQL Server source control tools that hook into SSMS, but I think they are just scripting the db objects and committing those scripts. Red Gate looks to be due to releasing such a tool this year for example.

有一些SQL Server源代码控制工具可以挂钩到SSMS，但我认为它们只是编写db对象的脚本并提交这些脚本。例如，Red Gate看起来是因为今年发布了这样一款工具。

#4

We just add the CREATE statement to source control in a .sql file, e.g.:

我们只是在.sql文件中将CREATE语句添加到源代码控制中，例如：

-- p_my_sp.sql
CREATE PROCEDURE p_my_sp
AS
    -- Procedure

Make sure that you only put one SP per file, and that the filename exactly matches the procedure name (it makes things so much easier to find the procedure in source control)

确保每个文件只放置一个SP，并且文件名与过程名称完全匹配（这使得在源代码管理中找到过程变得容易得多）

You then just need to be disciplined about not applying a stored procedure to your database that hasn't come from source control.

然后，您需要遵守不将数据库中的存储过程应用于源控制的规则。

An alternative would be to save the SP as an ALTER statement instead - this has the advantage of making it easier to update an existing database, but means you need to do some tweaking to create a new empty database.

另一种方法是将SP保存为ALTER语句 - 这样做的好处是可以更容易地更新现有数据库，但这意味着您需要进行一些调整以创建新的空数据库。

#5

I've been working on this tool http://timabell.github.com/sqlHawk/ for exactly that purpose.

我一直在研究这个工具http://timabell.github.com/sqlHawk/，正是出于这个目的。

The way to ensure no-one forgets to check in their updated .sql files is by making your build server force the staging and live environments to match source control ;-) (which this tool will assist you with).

确保没有人忘记签入更新的.sql文件的方法是让你的构建服务器强制登台和实时环境匹配源代码控制;-)（这个工具将帮助你）。

#6

2nd solution from @Darryl didn't work as suggested by @Moe. I modified @Darryl's template and I got it working, and thought it would be nice to share it with everybody.

来自@Darryl的第二个解决方案没有按照@Moe的建议工作。我修改了@ Darryl的模板并且让它工作了，并且认为与所有人分享它会很好。

IF NOT EXISTS(SELECT name FROM sysobjects
              WHERE name = '<Stored Proc Name>' AND type = 'P' AND uid = '1')   
    EXEC sp_executesql N'CREATE PROCEDURE dbo.<Stored Proc Name>
    AS
    BEGIN
        select ''Not Implemented''
    END
    '
GO

ALTER PROCEDURE dbo.<Stored Proc Name>
AS  
BEGIN
  --Stored Procedure Code 
End

This is really nice because I don't lose my stored procedure permissions.

这非常好，因为我没有丢失我的存储过程权限。

#1