Duplicate tables and procedures in master database

Question

I've just discovered, that master database contains duplicates of some other databases' tables and stored procedures.

For example, I have a database SomeDB with table justTable in it. For some reason, a copy of this table is in master as well, but with no data.

How is it possible? I'm certain that when I was creating those tables and procedures I was using corresponding databases and not master. They are all present in those databases (as they should be), just with a copy in master as well.

I use Microsoft SQL Server Management Studio 2014 with Redgate additions (source control and stuff).

Answer 1

Did you create the table with a script run through SSMS?

Is it possible that if you did you didn't include USE [SomeDB] at the top of the script and instead had the current DB set to master?

So doing this:

Instead of this:

You can see when a specific table was created by looking at the system views, as in:

SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , o.create_date
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type_desc = 'USER_TABLE';

This may help you understand where/when those tables were created.

Results from the query:

If you really need to prove where these are coming from, you probably want to create a DDL trigger at the server level to capture events such as CREATE TABLE etc, however that is beyond the scope of this answer. A Server-level DDL Trigger would allow you to capture all data-definition events for future events; i.e. it wouldn't help solve where these existing tables came from.