Modelling Database Creation with the Model System Database in SQL Server

Modelling Database Creation with the Model System Database in SQL Server

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
The Model database in SQL Server is used as the template for all the user databases that are created on a SQL Serve instance. Most of the times we do not modify the Model database however there are cases where it might come in handy.

For example consider the following scenario: For audit purposes, you want to set your SQL Server instance, each time a user database is created, to automatically create an audit-related table as well as a relevant stored procedure that will allow each application that uses a database to be able to store in a table (in each database) the log-in and log-out times for each user.

To do this, we can create the relevant table and stored procedure in the Model database:

USE [model]
GO

CREATE TABLE [dbo].[AuditLog](
[userID] [int] NOT NULL,
[loginTime] [datetime] NOT NULL,
[logoutTime] [datetime] NOT NULL
) ON [PRIMARY]
GO


CREATE PROCEDURE dbo.logUser
  @userID int,
  @loginTime datetime,
  @logoutTime datetime
AS 
BEGIN
  INSERT INTO dbo.AuditLog
VALUES (@userID, @loginTime, @logoutTime)
END
GO

Let’s take a look at the Model database:


Figure 1: The Model database after adding the table and stored procedure.


OK, so we have added the table and stored procedure in the Model database. Now let’s create three empty sample databases and check if the same tables and stored procedures are created automatically upon their creation:

CREATE DATABASE [SampleDB1];
GO
CREATE DATABASE [SampleDB2];
GO
CREATE DATABASE [SampleDB3];
GO

Time to check the three newly created databases and see if the “AuditLog” table and “logUser” stored procedures were automatically created:
Figure 2: SampleDB1
Figure 3: SampleDB2

Figure 4: SampleDB3

As you can see, all three databases were created fully based on the (modified) Model system database, thus the “AuditLog” table and “logUser” stored procedure were automatically created.

You can use the Model database for other things as well, as the above was just a simple example. However always have in mind when creating objects in the Model database that they will be automatically created for all user databases that will be created on that instance so be careful!

For more info on the Model database, please visit the following MSDN Library link.
P.S. Many thanks to my buddy Nakis for this great discussion. Always a pleasure man! 🙂


Recommended eBooks on SQL Server:

Developing with SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Developing SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.