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 SQL Server and T-SQL Development Tutorials
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 2016
download SQL Server 2014
download SQL Server 2012



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

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







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







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