Sql-server – Extra tables are added when I create a new Database

sql server

This is a really weird problem. When I create a new DB from a script I'm getting extra tables added. These aren't system type tables but regular tables that look like they may have come from a past project. There are 3 of them, each with 2 columns, either ints or nchar(10) with no keys, constraints, triggers, or indices.

I have no idea where this is coming from or how I can fix it. Right now I just delete the tables but it's a bit annoying and I'd like to fix the issue.

Here is a script I wrote that reproduces the error:

CREATE DATABASE X;
GO

USE X;
GO

CREATE TABLE Y(
  YId INT IDENTITY CONSTRAINT pk_yid PRIMARY KEY(YId));
GO

CREATE TABLE Z(
  ZId INT IDENTITY CONSTRAINT pk_zid PRIMARY KEY(ZId));
GO

After I run this script my DB is created, but it has 5 tables instead of 2.

Running SELECT * FROM INFORMATION_SCHEMA.TABLES just lists my extra tables as BASE TABLE, exactly the same as the ones I defined (all other columns are the same as well).

This is happening on SQL Server 2008 R2
(Microsoft SQL Server Management Studio 10.50.2500.0)

Best Answer

Someone added tables to the model database.

When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages.

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.

Related Question