Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

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.

share|improve this answer
    
Thanks, that was indeed the case. I removed the tables from the model database. –  Ryan Elkins Nov 16 '11 at 16:58
add comment

New databases are copies of the model database. If they're in model, they'll be in every new database you create.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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