SQL Server: How to Clear User Objects from Master DB

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.

  1. USE MASTER  
  2. GO  
  3.   
  4. SET NOCOUNT ON  
  5. GO  
  6.   
  7. SELECT N'ALTER TABLE [' + object_name(parent_object_id) + N'] DROP ' + [name] command from sys.objects where [type] = 'F' AND is_ms_shipped <> 1  
  8. UNION ALL  
  9. SELECT 'GO' command  
  10. UNION ALL  
  11. SELECT N'DROP PROCEDURE [' + [name] + N']' command from sys.objects where [type] = 'P' AND is_ms_shipped <> 1  
  12. UNION ALL  
  13. SELECT 'GO' command  
  14. UNION ALL  
  15. SELECT N'DROP TABLE [' + [name] + N'] ' command from sys.objects where [type] = 'U' AND is_ms_shipped <> 1  
  16. UNION ALL  
  17. SELECT 'GO' command  
  18. UNION ALL  
  19. SELECT N'DROP VIEW [' + [name] + N'] ' command from sys.objects where [type] = 'V' AND is_ms_shipped <> 1  
  20. UNION ALL  
  21. SELECT 'GO' command  
  22. UNION ALL  
  23. SELECT N'DROP FUNCTION [' + [name] + N'] ' command from sys.objects where [type] = 'FN' AND is_ms_shipped <> 1  
  24. UNION ALL  
  25. SELECT 'GO' command  
  26. UNION ALL  
  27. SELECT N'DROP FUNCTION [' + [name] + N'] ' command from sys.objects where [type] = 'TF' AND is_ms_shipped <> 1  
  28. UNION ALL  
  29. SELECT 'GO' command  

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

  1. SELECT count(1)  
  2. FROM master.sys.objects  
  3. WHERE is_ms_shipped <> 1 AND TYPE IN ('U','P','FN','V','TF')  
I hope you find it useful. Please share your comments. 
Ebook Download
View all
Learn
View all