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
  4. SET NOCOUNT ON
  5. GO
  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.