3

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).

6

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:

Bad

Instead of this:

Good

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:

enter image description here

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.

  • 2
    @retif Yes, this is the only thing that makes sense, especially if you can't reproduce the issue. If it were repeatable, I would have checked for a DDL trigger or a job that - for whatever reason - made a copy of every table you create. – Aaron Bertrand Jan 14 '16 at 14:35

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.