Model System DB in Microsoft SQL Server

There are 6 system databases:

1. Master
2. Model
3. MSDB
4. TempDB
5. Resource
6. Distribution

I simply want to share the most misunderstood database Model. The Model database is a template database. Whenever a new database is created, a copy of the Model database is created and renamed with the name of the database being created. The advantage is that all the objects available in Model will available to all the dbs created after those objects are created into the model db. For example, just in case you need to create a stored procedure or a function and need its availability across several databases, the best approach is to place them on the Model DB. To show this, explicitly create a dbo.Msg function in Model database, following which create two databases MyDB & DEMO. Now this function will be available from within the databases created after the function.

USE Model
GO
CREATE FUNCTION dbo.Msg (@String VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
SET @String='Running this from Model for '+@String+' Database' 
RETURN @String
END

CREATE DATABASE MyDB;
USE MyDB
GO
SELECT dbo.Msg('MyDB');
-----------------------------------------
Running this from Model for MyDB Database

(1 row(s) affected)

CREATE DATABASE DEMO;
USE DEMO
GO
SELECT dbo.Msg('DEMO');
-----------------------------------------
Running this from Model for DEMO Database

(1 row(s) affected)

After creating this function in the Model database, it will be propagated to all databases created after adding it to the Model database and can be utilized with the simplified code. Conversely, the databases created prior to the creation of the function will not be able to access it. In this example, the database TestDB already existed prior to the creation of the function dbo.Msg on Model database, hence the below query throws an error.

USE TestDB
GO
SELECT dbo.Msg('TestDB');
-----------------------------------
Msg 4121, Level 16, State 1, Line 1

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Msg", or the name is ambiguous. Any database object can be added to the Model database so that they are available in subsequently created databases. This includes database users, roles, tables, stored procedures, functions, and assemblies.