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

Question

我们为每个客户都有一个单独的数据库。它们都有相同的表和存储过程。问题在于当我们需要更新存储过程时，我们必须确保将其更新到所有数据库中。当然，可能会忽略一个数据库上的一个存储过程而没有进行更新。

我考虑在 master 中创建一个存储过程，并在前缀加上 sp_，同时使用 sys.sp_MS_marksystemobject 将对象标记为系统对象。这似乎可行... 但是，这篇 article 文章说：“这种解决方案不建议用于实时数据库服务器，你可以在开发和测试服务器中使用它来加速开发和测试。”

如果是这样，那么生产环境中最好的解决方案是什么？

- alpha

---

你应该好好看看Red-Gate MultiScript，这是一个非常棒的工具，可以一次性在多个服务器（或数据库）上部署多个脚本。这可能会给你省去很多麻烦！ - marc_s

谢谢！这看起来是一个不错的解决方案。 - alpha

Answer 1

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

-- 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

你也许可以通过从sys.sql_modules中提取过程定义来轻松地实现某些操作，但在执行CREATE与ALTER时可能会遇到一些复杂情况。

- Eilert Hjelmeseth

---

谢谢！我们这里有类似的东西。我认为Red Gate的解决方案可能会少很多烦恼！ - alpha

你可以使用 SET @sql = 'CREATE OR ALTER ' + @sp_code; 不过最好使用删除/创建操作，因为这样可以强制 SQL Server 刷新对象的执行计划。 - Hugo Vares

Answer 2

补充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)

- Ddddan