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
)
as
begin
set @output_date = try_convert(varchar, @input_date, 103)
end
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
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