8

When I add a new database to our dev server (using SSMS), it automatically creates 5 tables.

These are copies of application tables that already exist in another database.

I assume this is some recent SQL Server feature I don't know about where tables can be marked as 'common' or some such, and so get added automatically to new databases.

But how can I stop it happening?

For the avoidance of confusion, this database is not scripted, it is created directly from the SSMS GUI (right-click Databases > New Database)

(moved from StackOverflow)

6
  • 4
    Check out the model database from system databases. Does it contain the tables too?
    – vonPryz
    Sep 7 '16 at 11:00
  • Yes the same tables - I assume that's the cause? Sep 7 '16 at 11:04
  • Yes, model is "model" database that is used as a source for every next database created.
    – Marcin S.
    Sep 7 '16 at 11:06
  • Wow I never knew that (only been using SQL Server since version 6.3) Sep 7 '16 at 11:07
  • The feature has certainly been there since at least SQL Server 2005 (possibly earlier), though.
    – Andriy M
    Sep 7 '16 at 11:08
15

This happens (based on the discussion in comments) because the tables exist in the model system database. As per the documentation (my emphasis):

If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures. File properties of the model database are an exception, and are ignored except the initial size of the data file.

As a solution, dropping the tables from model will stop this phenomenon.

1
  • MIght be worth adding that there are some exceptions, like TempDB recreation after server restart. In that case, TempDB uses all own file properties, including initial size of data files.
    – Marcin S.
    Sep 7 '16 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.