Quick Scan Report – User Tables in Model

It is generally considered bad practice to create user tables in the model database. The model database serves as a template for new databases—any changes made to it, including user tables, will be copied to every new database created on the server.

Why This Is a Problem

Occasionally, someone might think it is a good idea to add their own table to the model database. This may go unnoticed for years until one of the following issues arises:

  • Every new database will inherit the user tables – Any new database will include those tables, even when they are not needed.
  • Unexpected behavior and conflicts – Scripts expecting a clean database structure may fail or behave unpredictably.
  • Complications when moving to a new SQL Server – If you migrate databases and expect them to be clean, you may run into unexpected dependencies.

What Should You Do?

If you have user tables in the model database, it is recommended that you:

  1. Identify User Tables
    Run the following query to check for user tables in the model database: SELECT name FROM model.sys.tables;
  2. Move Tables to the Appropriate Database
    If you find user tables in the model database, determine where they should be stored and move them to the correct user database.
  3. Clean Up the Model Database
    Once the tables have been moved, remove them from the model database: USE model; DROP TABLE [TableName]; -- Replace with the actual table name

Summary

User tables should not be created in the model database, as they will be copied to every new database on the server. This can lead to unintended issues, conflicts, and complications when migrating to a new SQL Server. It’s best to keep the model database clean and store user tables in appropriate databases.

Contact Stedman Solutions

If you need help identifying or cleaning up unnecessary tables in system databases, Stedman Solutions can assist. Our team specializes in SQL Server best practices, performance tuning, and database management to keep your systems running efficiently.

Contact Stedman Solutions Today to get expert assistance with your SQL Server environment!