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