SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example

If you have a stored procedure which you use for database maintenance or database tracking you may want to create this t-sql stored procedure on every database in the MS SQL Server instance.
Since you want to make the deployment of a sql stored procedure easier, and you want to create stored procedure on all databases in one click, you will have to use a tool for SQL Server which will deploy the sp to each database for you.
Or you will develop a t-sql script which will create the stored procedure for you on all sql databases.

I'll try to explain an sql example script which I'm using to make such deployments in my development and production database environment.

One last note as an update to this SQL tutorial, please review the tutorial execute store procedure named with sp_ prefix in master database from any database in a SQL Server instance. This method can be a better alternative for most requirements to fit easier maintenance and single point of deployment.
Besides, since sp_MSForEachDB is an SQL Server undocumented stored procedure many authorities don't approve too much use of sp_MSForEachDB especially in production environments because of possible memory leaks, etc. Of course it is your own risk to use sp_MSForEachDB or not.

Let's continue this T-SQL tutorial. Let's start to build a sample stored procedure which we want to deploy on every sql database installed in the MS SQL Server.

CREATE PROC TestSpForAllDBs AS SELECT 1

Create this t-sql stored procedure in master database.
Now we will create a deployment stored procedure which will get the text of this database object TestSpForAllDBs, and will execute this text on every database on the sql instance.

Please note that the below stored procedure named "CreateProcedure" will be created in master database of the SQL Server.

The stored procedure creator procedure :) takes two parameters.
First parameter @dbname is the database name parameter. We will use database name parameter as an identifier showing the target database of this deployment.
The second parameter @spname is the name of the stored procedure which we want to deploy on every sql database. For our sql example this sp name will be "TestSpForAllDBs".

CREATE PROC CreateProcedure
(
  @dbname sysname,
  @spname sysname
)
AS

SELECT @dbname = REPLACE(REPLACE(@dbname,'[',''),']','')

IF @dbname <> 'master'
BEGIN

DECLARE @proc_text nvarchar(max)

SELECT
  @proc_text = REPLACE([text],'''','''''')
FROM [sysobjects] o
INNER JOIN [syscomments] c
  ON c.id = o.id
WHERE
  o.type = 'P' AND
  o.name = @spname

DECLARE @sql nvarchar(max)
SET @sql = 'USE ' + @dbname + '; EXEC ('' ' + @proc_text + ''');'

EXEC sp_Executesql @sql

END

GO

If you take a look at the above t-sql source code of the CreateProcedure, you will notice that :
We are reading the text or create code of the stored procedure into a nvarchar(max) parameter;
Then we create a dynamic t-sql command code. This dynamic t-sql code has the following properties :
First it executes a USE databasename; syntax in order to change the executing database.
Later, it runs the create procedure command text by using the EXEC('sqlcommand') syntax.

The above part of the solution only creates the stored procedure on a given target database.
We have to manually specify the database name.
The solution of this problem is actually very easy by using the sp_MSForEachDB undocumented stored procedure.

All we have to do for a complete solution is as simple as calling the below sp_MSForEachDB command.

EXEC sp_MSForEachDB 'CreateProcedure ''[?]'', ''TestSpForAllDBs'''

As you see, the above t-sql sp_MSForEachDB statement will execute the CreateProcedure stored procedure on master database for each database in the MS SQLServer instance.
Each execution will have a different database name value for the @dbname parameter.
And this difference will enable us deploy our example sql stored procedure on every database on the SQL Server.

Special Thanks to Lilly,
And one last note, many thanks to Lilly who has warned me about replacing the single quotes in the target sql stored procedure with double quotes. Otherwise the execution of the CreateProcedure stored procedure will cause sql error "Incorrect syntax near ..." for sql procedures which has quotes in its text definition.
Many thanks for her right warning which let me to a better sql solution.


More Tutorials on T-SQL sp_MSForEachTable Examples

sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database
sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
Listing All MS SQL Server Databases Using T-SQL
SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications
Create Database Snapshot for all Databases on SQL Server






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems