Click here to Skip to main content
12,816,775 members (33,458 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


3 bookmarked
Posted 30 May 2011

Model System DB in Microsoft SQL Server

, 31 May 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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
SET @String='Running this from Model for '+@String+' Database' 
RETURN @String

SELECT dbo.Msg('MyDB');
Running this from Model for MyDB Database

(1 row(s) affected)

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.
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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Keshav Singh
Database Developer
India India
I am a Microsoft certified Technology Specialist in MS SQL Server 2008 and 2005.I have fair amount of experience and expertise in MS database development, administration and modeling and MS BI. Started my career in 2007 and primarily into MS databases, and later diversified into MS BI especially SSIS. I also have a little exposure in Oracle 10g while working on one of the migration projects. But MS SQL Server is my passion!

Firm believer in knowledge grows from sharing, I really like reading books, trying new features and sharing the little that I know. Unless we're willing to have a go, fail miserably, have another go, success won't happen. The best thing I have discovered about myself is the "never say die" attitude. The best comment I have ever received is when my manager at my first job said "when this guy works he throws away his watch".

You may also be interested in...


Comments and Discussions

GeneralCrisp Explanation. Thanks for this Pin
puja1119125-Apr-14 18:01
memberpuja1119125-Apr-14 18:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170308.1 | Last Updated 31 May 2011
Article Copyright 2011 by Keshav Singh
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid