SQL Server: Create Objects Automatically in New Databases

Suppose whenever a new database is created, you also want to create a stored procedure automatically (by default) in this new database. Let us see the simplest way to do this.

The Model database is a system database which SQL Server uses as a model to create new databases. So if you create any object in the model database, that object will be created automatically in all newly-created databases. Let us see this with the following examples

Create this test procedure in model database

use model
GO
create procedure test
as
select 100 as number

Now execute this procedure

EXEC test

It displays the value 100.
Now create another database named test

CREATE database test
GO

Now execute the same procedure in test database

use test
GO
EXEC test

As you can see, executing the above statement displays the value 100 since this value was taken from the stored procedure in the model database and is now available with test database too.

Feel free to share other approaches to create objects automatically in a database.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan