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.