There are 6 system databases:
-
Master
-
Model
-
MSDB
-
TempDB
-
Resource
-
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.
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".