Earlier this week I ran a script that inserted a lot of objects into a (new) database on my local machine, but forgot the USE newdatabase statement at the top. You guessed it… all my objects were created in the master database, and there were a lot of them. Deleting them by hand would be way too tedious.
I found this blog from Prakash Tripati (credits to him!) that did almost what I want, but his version did not take schema names into account, so I approved upon it a little. See below.
USE MASTER
GO
SET NOCOUNT ON
GO
SELECT N'ALTER TABLE '+schema_name([schema_id])+N'.[' + 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 '+schema_name([schema_id])+N'.[' + [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 '+schema_name([schema_id])+N'.[' + [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 '+schema_name([schema_id])+N'.[' + [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 '+schema_name([schema_id])+N'.[' + [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 '+schema_name([schema_id])+N'.[' + [name] + N'] ' command from sys.objects where [type] = 'TF' AND is_ms_shipped <> 1
UNION ALL
SELECT 'GO' command
This generates the statements in the right order in the results view. Then you can simply copy the result and paste it into a query window and execute it (on master ! 😉 ).
Geef een reactie