How to Create a Table Automatically in All Newly Created Databases? – Interview Question of the Week #238

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.

• How to Skip Top N Rows in SQL Server? – Interview Question of the Week #237
• How to Write in Text File from T-SQL in SQL Server? – Interview Question of the Week #236
• How to Remove Quotes of QUOTENAME? – Interview Question of the Week #235
• Can an Index Reduce Performance of SELECT Query? – Interview Question of the Week #234
• How to Find Definition of Computed Column in SQL Server using T-SQL? – Interview Question of the Week #233
• What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232
• What is Faster, SUM or COUNT? – Interview Question of the Week #231

Reference: Pinal Dave (https://blog.sqlauthority.com)