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

Question: How can a table appear automatically in newly created databases? On a SQL Server instance, put the approved object in model, the template for ordinary new databases.

A client asked this during a performance health check. They had an auditing table that belonged in existing databases and wanted future databases to receive it too. It is a good interview question because the answer is a system database that many people rarely touch.

User-defined tables, views and procedures added to model are copied into ordinary databases subsequently created on that instance. Applicable database options are also inherited. Model does not copy instance-level server settings into every database.

The timing matters. Adding a table to model does not add it to databases that already exist. Those need a separate controlled deployment. A restored or attached database brings its own objects; it isn’t freshly populated from today’s model. Removing a template object later doesn’t remove copies already created.

Review the object name, schema, permissions and any initial rows before changing model. Data in a template table is copied as well, so a table intended to start empty should actually be empty. A shared template can quietly spread a mistake just as efficiently as the intended design.

For application-specific auditing, a database provisioning script can be easier to version and select than modifying the template for every new database. Model is useful when the object genuinely belongs in every ordinary database on that instance. You don’t need to change model to demonstrate the interview answer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.