Question: How to Create a Table Automatically in All Newly Created Databases?
Answer: Honestly, I would have asked this question in the interviews as I believe quite many people would not know the answer to this question. The matter of fact, this question was recently asked in my recent consulting engagement Comprehensive Database Performance Health Check.
Quite often I encounter a situation during SQL Server Consulting when there is a special stored procedure, function or a table I have to create in the database for either auditing purpose. The table usually has to be created for all the existing databases and also for the new databases.
In such situations, the best solution is to create a table in the Model database. Anything which you create inside the Model database is automatically created when any new database is created. The matter of fact the way Model database works is that it serves as a template for any new databases. Any new database is created as a replica of the model database and it inherits all the properties from file growths to server settings.
If you want any objects to be created in all the new database, create them in the Model database and it will create a table automatically, re-created in the new databases.
Here are a few other interview questions and answers you may find interesting.
Reference: Pinal Dave (https://blog.sqlauthority.com)