There are 6 system databases:
I simply want to share the most misunderstood database
database is a template database. Whenever a new database is created, a copy of the
database is created and renamed with the name of the database being created. The advantage is that all the objects available in
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
DB. To show this, explicitly create a
database, following which create two databases
. Now this function will be available from within the databases created after the function.
CREATE FUNCTION dbo.Msg (@String VARCHAR(MAX))
SET @String='Running this from Model for '+@String+' Database'
CREATE DATABASE MyDB;
Running this from Model for MyDB Database
(1 row(s) affected)
CREATE DATABASE DEMO;
Running this from Model for DEMO Database
(1 row(s) affected)
After creating this function in the
database, it will be propagated to all databases created after adding it to the
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
already existed prior to the creation of the function
database, hence the below query throws an error.
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "
" or the user-defined function or aggregate "
", or the name is ambiguous.
Any database object can be added to the
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".