Create Objects Automatically in New Databases in sql server

Suppose whenever a new database is created, we also want to create a database object like stored procedure, table or function automatically (by default) in this new database. We can create it by using Model database. This is the system database.

Read System database: click here

http://bageshkumarbagi-msbi.blogspot.in/2016/08/system-database-in-sql-server-2012.html

Model database is store the template for the all user defined database. User define object may be table, view, functions or Stored procedures. All can be can be created in the model database and will exist in all future user define database. If we want to keep some generic database objects like as tables, function stored procedures into the newly created database then we put these objects into Model database. Hence when we create a new database then available database objects in Model database, would be copied into newly created database.

See the example.

I am creating an audit table, a function and a stored procedure in Model database.

--Use Modal system database Use Model -- Audit table create table tblAudit ( Id int identity(1,1), audit_description varchar(100) ) --User define function CREATE FUNCTION dbo.fnGet_Year(@Date AS DateTime) RETURNS INT AS BEGIN     RETURN YEAR(@Date) END --creating Sp create procedure Modal_Test as select 100 as number

All object s are created in Model data

Now I am creating a new database.

  

Click ok. New database is created successfully.

Now I am seeing the object in newly created database.