更新多个数据库上的存储过程

We have a separate database for each of our customers. They all have the same tables and stored procedures. The problem is when we have to update a stored procedure, we would have to make sure that we update it for all the databases. Of course, one stored procedure on a database could have been overlooked and not updated.

我们为每位客户提供单独的数据库。它们都具有相同的表和存储过程。问题是当我们必须更新存储过程时，我们必须确保为所有数据库更新它。当然，数据库上的一个存储过程可能会被忽略而不会更新。

I've looked at creating a stored procedure in the master and prefixed it with sp_ and also set marking the object as system object using sys.sp_MS_marksystemobject. This appears to work.... however, this article says " This solution is not recommended for live database servers, you can use it in the development and testing server to expedite your development and testing."

我已经看过在master中创建一个存储过程并用sp_作为前缀，并且还使用sys.sp_MS_marksystemobject将对象标记为系统对象。这似乎有用......但是，本文说“不建议将此解决方案用于实时数据库服务器，您可以在开发和测试服务器中使用它来加速开发和测试。”

If that is the case, what would be the best solution for production?

如果是这样的话，什么是生产的最佳解决方案？

2 个解决方案

#1

If the goal is basically just to deploy a stored procedure to every client database, something like this script should work.

如果目标基本上只是将存储过程部署到每个客户端数据库，那么像这样的脚本应该可以工作。

-- put the entire stored procedure code in a variable
-- have it start with "PROC" so we can easily either create or alter the 
-- procedure based on whether it already exists or not
DECLARE @sp_code NVARCHAR(MAX) = 
'
 PROC [dbo].[usp_some_proc] AS
SELECT DB_NAME()
'

-- get a list of databases to install the stored procedure to
SELECT
    [name]
INTO #tbl_databases
FROM sys.databases
WHERE   [name] LIKE 'db[_]client[0-9]'

-- define some variables to use in the loop
DECLARE @sql NVARCHAR(MAX);
DECLARE @execute_sql NVARCHAR(MAX);
DECLARE @database_name NVARCHAR(500);

-- iterate through each database
WHILE EXISTS (SELECT * FROM #tbl_databases)
BEGIN

    -- get this iteration's database
    SELECT TOP 1
        @database_name = [name]
    FROM #tbl_databases

    -- determine whether stored procedure should be created or altered
    IF OBJECT_ID(QUOTENAME(@database_name) + '.[dbo].[usp_some_proc]') IS NULL
        SET @sql = 'CREATE' + @sp_code;
    ELSE
        SET @sql = 'ALTER' + @sp_code;

    -- define some dynamic sql to execute against the appropriate database
    SET @execute_sql = 'EXEC ' + QUOTENAME(@database_name) + '.[dbo].[sp_executesql] @sql';

    -- execute the code to create/alter the procedure
    EXEC [dbo].[sp_executesql] @execute_sql, N'@sql NVARCHAR(MAX)', @sql;

    -- delete this database so the loop will process the next one
    DELETE FROM #tbl_databases
    WHERE   [name] = @database_name

END

-- clean up :)
DROP TABLE #tbl_databases

You could maybe do something slick with pulling the procedure definition out of sys.sql_modules, but might run into some complications with doing a CREATE vs. ALTER.

您可以通过从sys.sql_modules中提取过程定义来执行某些操作，但是在执行CREATE与ALTER时可能会遇到一些复杂问题。

#2

To add to Eilert's excellent answer, if you are copying the item from an existing database, you can obtain the relevant portion of the object's text (in this case the Stored Procedure) by the following:

要添加Eilert的优秀答案，如果要从现有数据库复制项目，则可以通过以下方式获取对象文本的相关部分（在本例中为存储过程）：

DECLARE @RawDefinition NVARCHAR(max) = 
   OBJECT_DEFINITION (OBJECT_ID(N'[source_db].[dbo].[usp_some_proc]'))
SELECT PATINDEX('%PROCEDURE%', @RawDefinition)
DECLARE @sp_code NVARCHAR(max) = ' ' + Right(@RawDefinition, Len(@RawDefinition) - 
   PATINDEX('%PROCEDURE%', @RawDefinition) + 1)

#1