SQL Server: How to Clear User Objects from Master DB

This blog explains the ways to clear the user objects from Master Database in SQL Server.

I am again here to discuss one issue I got and how I fixed it.

While testing the database scripts, I missed adding USE <MY DB Name> and my all user objects got created in Master database.

and these objects were more than 100.

 

Now what to do?

 

The main issue was how to delete tables in the right order, I mean child table first and then parent tables and more than that, how to clean my master database?

 

After some research I figured out ways to clear them. It first removes the foreign key constraints and then other objects and work like a charm.

This generates the drop statements in the right sequence then you can simply copy the result into another query window and execute it.

USE MASTER
GO

SET NOCOUNT ON
GO

SELECT N'ALTER TABLE [' + object_name(parent_object_id) + N'] DROP ' + [name] command from sys.objects where [type] = 'F' AND is_ms_shipped <> 1
UNION ALL
SELECT 'GO' command
UNION ALL
SELECT N'DROP PROCEDURE [' + [name] + N']' command from sys.objects where [type] = 'P' AND is_ms_shipped <> 1
UNION ALL
SELECT 'GO' command
UNION ALL
SELECT N'DROP TABLE [' + [name] + N'] ' command from sys.objects where [type] = 'U' AND is_ms_shipped <> 1
UNION ALL
SELECT 'GO' command
UNION ALL
SELECT N'DROP VIEW [' + [name] + N'] ' command from sys.objects where [type] = 'V' AND is_ms_shipped <> 1
UNION ALL
SELECT 'GO' command
UNION ALL
SELECT N'DROP FUNCTION [' + [name] + N'] ' command from sys.objects where [type] = 'FN' AND is_ms_shipped <> 1
UNION ALL
SELECT 'GO' command
UNION ALL
SELECT N'DROP FUNCTION [' + [name] + N'] ' command from sys.objects where [type] = 'TF' AND is_ms_shipped <> 1
UNION ALL
SELECT 'GO' command

You can verify the counts before and after using below query.

SELECT count(1)
FROM master.sys.objects
WHERE is_ms_shipped <> 1 AND TYPE IN ('U','P','FN','V','TF')

I hope you find it useful. Please share your comments.