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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

Create Store Procedure that will Run in All Databases

In order to create a stored procedure that will run in all databases on a SQL Server instance, create stored procedure in master database and name it starting with "sp_"

Naming stored procedures in master database starting with "sp_" prefix enables SQL developers to call these stored procedures in any of the database on the related SQL Server instance. Developers can prefer this method for stored procedures that manages basic operations. Be careful using this method and do not turn master database into a garbage of stored procedures serving only for one or two databases on your SQL Server instance.

This prevents SQL developers from the requirement to create the same stored procedure in every database using a method like described at Create Same Stored Procedure on All Databases using sp_MSForEachDB. Of course also calling a single stored procedure makes the maintenance more easier. At least as a developer, there will be only one point of interest for maintenance and enhancement.

Let's start with an example. I deal with converting datetime and date values into Turkish date and time format frequently. I created a simple stored procedure in master database whose name is starting with "sp_" prefix as follows

CREATE PROC sp_TurkishDateFormat (
 @input_date date,
 @output_date varchar(10) output
 set @output_date = try_convert(varchar, @input_date, 103)

After this sp_* stored procedure is created in master database, I can call it in any database as seen in below sample script.
For example, you can switch to AdventureWorks sample database by executing: USE AdventureWorks
Then run following SQL script

DECLARE @input_date datetime = GETDATE(), @output_date varchar(10)
EXEC sp_TurkishDateFormat @input_date, @output_date out
SELECT @output_date DateInTurkishFormat

create stored procedure with sp_ prefix
If you create and name a stored procedure with sp_ prefix in master database, you can execute it from all databases on the related SQL Server instance

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.