How to drop user defined objects from master database

sql-server, sql-server-2008, sql-server-2008-r2

Solution

I have just created this Script to create a Script to drop all User Defined Objects in you Master database just test it on Dev server before you execute it on Production server ..

SELECT
    'DROP ' + CASE WHEN  type = 'U' THEN 'TABLE '
                   WHEN  type = 'P' THEN 'PROCEDURE '
                   WHEN  type = 'FN'THEN 'FUNCTION '
                   WHEN  type = 'V'THEN 'VIEW ' END
     + QUOTENAME(s.[name]) + '.' + QUOTENAME(o.[name]) + CHAR(10) + 'GO' + CHAR(10)

FROM        master.sys.objects o 
INNER JOIN  master.sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[is_ms_shipped] <> 1
  AND o.[type] IN ('U','P','FN','V')

-- Results to Text --

Generate Script

DROP TABLE [dbo].[Test_table1]
GO

DROP PROCEDURE [hr].[usp_Test_Proc1]
GO

DROP VIEW [views].[vw_TestView_1]
GO

DROP PROCEDURE [dbo].[usp_Test_Proc2]
GO

DROP FUNCTION [dbo].[udf_Test_Function_GetList]
GO

Note

If the generated Script tries to delete a table that is referenced by other table via Foreign Key it will throw an error.

Problem

I am using `SQL Server 2008 R2`. I had created an `SQL Script` for my project's database i.e. used to create all the tables, constraints, views, stored procedures and functions with some minimal data for creating a fresh database. But by mistake, I had ran it on the `master` database. So that all of those stuff were created in `master` database. Now, I want to `drop` all that `user-defined objects` from `master` database. Is there any easy way to do it?

Original source