Updating a stored procedure in 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 that when we need to update the stored procedure, we need to make sure that we update it for all databases. Of course, one stored procedure in the database could be missed and not updated.

I looked at creating a stored procedure in master and using prefixes using sp_ , and also set the marking of the object as a system object using sys.sp_MS_marksystemobject . This seems to work ... however, this article says: "This solution is not recommended for live database servers, you can use it in development and a testing server to speed up development and testing."

If so, what would be the best production solution?

Answer 1

If the goal is simply to deploy the stored procedure in each 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 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 

Maybe you can do something stain by pulling the procedure definition out of sys.sql_modules, but there may be some complications when doing CREATE vs. ALTER.