Check the database context before deciding where a script created its objects. Finding user objects in master starts with an inventory, not a DROP statement. Some objects there serve a deliberate server purpose.
Confirm the Connection and Database Context
A query window's database selection matters when the script does not specify its destination. CREATE TABLE and CREATE PROCEDURE act in the current database. A successful execution proves the statement worked, not that the object landed where the application expects it.
I check the server name and DB_NAME before examining unfamiliar objects. That prevents a cleanup discussion from starting on the wrong instance. The master database stores important server metadata. Treat its contents as an administrative inventory, even when a table name looks obviously misplaced.
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS CurrentDatabase;
SELECT s.name AS SchemaName, o.name AS ObjectName,
o.type_desc, o.create_date, o.modify_date
FROM master.sys.objects o
JOIN master.sys.schemas s ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
AND o.type IN ('U','P','V','FN','IF','TF','TR')
ORDER BY o.create_date DESC, s.name, o.name;The filter identifies objects not marked as shipped system objects. It does not classify them as mistakes. A server helper created intentionally by an administrator belongs in the same inventory. Metadata visibility also depends on permissions, so an empty result is not proof that the database contains no user objects.
Inventory User Objects in master by Type
Different object types need different review questions. A table can hold persistent data. A procedure can be invoked by jobs, applications, or server startup behavior. Creation and modification dates help reconstruct activity, but they do not identify the person or process responsible.
SELECT s.name AS SchemaName, t.name AS TableName,
t.create_date, t.modify_date
FROM master.sys.tables t
JOIN master.sys.schemas s ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0;
SELECT s.name AS SchemaName, p.name AS ProcedureName,
p.create_date, p.modify_date, p.is_auto_executed
FROM master.sys.procedures p
JOIN master.sys.schemas s ON s.schema_id = p.schema_id
WHERE p.is_ms_shipped = 0;The is_auto_executed flag identifies procedures configured to run when SQL Server starts. That is a reason for deeper review, not immediate removal. Startup procedures require an administrator's understanding of their server purpose. Moving one to an application database does not preserve that behavior automatically.
Inspect permissions and execution context alongside the definition. An object in master can depend on server-level administrative behavior. Copying its text elsewhere does not automatically reproduce its effective security model.
Capture User Objects in master With Their Dependencies
Read module definitions before choosing a destination. The next query stays in master and lists dependency metadata for its user modules. Dynamic SQL and application calls are outside the complete reach of this catalog. Treat the result as a starting map rather than a complete usage report.
USE master;
SELECT s.name AS SchemaName, o.name AS ObjectName,
m.definition
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE o.is_ms_shipped = 0;
SELECT OBJECT_SCHEMA_NAME(d.referencing_id) AS ReferencingSchema,
OBJECT_NAME(d.referencing_id) AS ReferencingObject,
d.referenced_server_name, d.referenced_database_name,
d.referenced_schema_name, d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON o.object_id = d.referencing_id
WHERE o.is_ms_shipped = 0;Encrypted modules do not expose their definition through this query. Do not treat a NULL definition as empty code. Locate the authorized deployment source or ask the owner for it. Also inspect table indexes, constraints, computed columns, triggers, and permissions through SSMS scripting.
Prepare the Destination Without Removing the Source
Use SSMS to script each approved object with its dependencies and permissions. Choose a query window or saved script as the output. Inspect the generated USE statement and change it to the intended existing application database. Confirm the destination server before running any creation script.
A table move includes its data, not just its CREATE statement. Decide how to copy rows, preserve identities, and validate constraints while controlling concurrent writes. For a large or active table, plan a maintenance window or another approved transfer method. This inventory article does not provide a universal cutover script.
Do not rename a database context and assume every three-part reference follows it. Review hardcoded master references inside modules and callers. Preserve the original scripted definitions and source data before testing. A misplaced filing cabinet still contains real papers; its location is not permission to empty it.
Test With a Harmless Destination Object
The following small example demonstrates explicit context selection. Run it only after confirming the disposable destination exists. It creates a fresh demonstration table and procedure there. It does not copy or remove an object from master.
USE [YourDisposableDatabase];
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DestinationDatabase;
CREATE TABLE dbo.ContextMoveDemo
(DemoID int NOT NULL PRIMARY KEY, DemoValue int NOT NULL);
INSERT dbo.ContextMoveDemo VALUES (1,10),(2,20);
GO
CREATE PROCEDURE dbo.ReadContextMoveDemo
AS
BEGIN
SET NOCOUNT ON;
SELECT DemoID, DemoValue FROM dbo.ContextMoveDemo ORDER BY DemoID;
END;
GO
EXEC dbo.ReadContextMoveDemo;Replace the database name with an approved test database before execution. Keep GO around the procedure definition. In a real move, compare source and destination rows, keys, object definitions, and permissions. Row counts alone do not prove every value was transferred correctly.
Find the Callers Before Planning Cutover
Check SQL Agent job steps, application configuration, and administrative scripts for object references. A procedure called with master.dbo.ProcedureName remains tied to master regardless of the caller's default database. An unqualified call has different resolution rules and deserves its own test.
I review both known callers and the object's owner before proposing removal. Usage monitoring over a representative business interval can reveal periodic tasks missed by a short check. Which process would fail if the original object disappeared tomorrow?
A deliberate administrative helper can remain in master with documented ownership. An application object can move after its destination and callers have been tested. The inventory should record that decision per object rather than applying one rule to every non-shipped item.
Approve Removing User Objects in master Separately
Keep the original available until the cutover has been verified and rollback requirements are clear. Removal needs explicit approval, a preserved definition, and any required data backup. This article intentionally ends before generating a destructive cleanup batch.
After the approved move, confirm the application uses the destination and that scheduled tasks succeed. Then record the final location and owner in deployment instructions. Add an explicit database context to future creation scripts. That small guard prevents the same accident without turning master into a recurring cleanup project.
Preserve the Evidence Behind the Decision
Save the inventory with its server name, capture time, and reviewing owner. Include why each object remains, moves, or needs further investigation. That record is more useful than an unexplained list of removed names. It also keeps an intentional startup procedure from being rediscovered as an apparent mistake later.
For table data, preserve a recoverable copy before transfer. For modules, preserve permissions and session settings with the definition. The destination test should cover required behavior under the application's actual login, not only under an administrator. Successful execution with elevated permissions can conceal a missing grant. Keep unresolved dependencies visible until their owners confirm the next step. User objects in master need individual ownership and caller checks. Retain that evidence when deciding which user objects in master are deliberate and which belong elsewhere.
Related reading on this blog: What Breaks If I Drop This Column? sys.sql_expression_dependencies and Quick Introduction to Startup Procedures.
A user object in master is not automatic junk, it is an item whose purpose and callers need verification before any move or removal.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
