Update stored procedure on multiple databases

Question

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

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

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