HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

SQL Server: Create default stored procedure as and when a new database is created

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storednewcreatesqlcreatedandproceduredatabasedefaultwhen

Problem

I am using SQL Server 2008 R2. I need one simple stored procedure to be created by default, as and when a new database is created in SQL Server. How can I achieve this? Assume the stored procedure to be created is

CREATE PROCEDURE [dbo].[Default_SP]
AS
BEGIN
    SELECT GETDATE()
END


Triggers can be applied on the table level. But this is on database create event. I searched over the net and I could not find answers.

Solution

Create the procedure in the system database model.

From model Database


The model database is used as the template for all databases created
on an instance of SQL Server.

Context

StackExchange Database Administrators Q#94347, answer score: 21

Revisions (0)

No revisions yet.