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
create procedure test
select 100 as number
Now execute this procedure
It displays the value 100.
Now create another database named test
CREATE database test
Now execute the same procedure in test database
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.