Modelling Database Creation Using the Model System Database in SQL Server

In this article, we will be discussing about the process of modelling database creation, using the Model system database in SQL Server. The Model database in SQL Server is used as the template for all the user databases that are created on a SQL Server instance. Most of the times, we do not modify the Model database, however there are cases where it might come in handy.

 

How to Use the “Model” System Database – Example

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:

Modelling Database Creation with the Model System Database in SQL Server - SQLNetHub
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:

 

Database SampleDB1:

Modelling Database Creation with the Model System Database in SQL Server - SQLNetHub

 

Database SampleDB2:

Modelling Database Creation with the Model System Database in SQL Server - SQLNetHub

 

Database SampleDB3:

Modelling Database Creation with the Model System Database in SQL Server - SQLNetHub

 

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 about the “Model” system database in SQL Server, please visit the following MS Docs article.

Learn More Tips like this – Enroll to the Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Upgrade your Tech Skills – Learn all about Azure SQL Database

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Introduction to Azure SQL Database (Online Course - Lifetime Access)
(Lifetime Access/ Live Demos / Downloadable Resources and more!)
Learn More

 

Read Also

 

Check our Database Security and Administration Tool: DBA Security Advisor

DBA Security Advisor: Secure your SQL Server instances by scanning multiple instances against a rich set of security checks, and by getting recommendations and remediation steps.

DBA Security Advisor - SQL Server Security and Administration Tool

Learn more

 

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

Benchmark SQL Server memory-optimized tables with In-Memory OLTP Simulator.

 

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub