How to drop user defined objects from master database

Question

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?

Answer 1

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.

Answer 2

Please refer to this link for scripts for drop constraints and tables. http://www.toadworld.com/platforms/sql-server/w/wiki/10407.delete-user-objects-from-a-database.aspx However, it doesn't work with tables defined in a schema and required me to do some changes. Please find the modified code below:

-- DROP Foreign Keys
SET NOCOUNT ON
DECLARE @fk_id int, @fk_name nvarchar(500), @parent_id int, @parent_name nvarchar(500), @schema_name nvarchar(500)
declare @sql nvarchar(2000)

DECLARE fk_cursor CURSOR FOR
SELECT [name] as fkname, schema_name([SCHEMA_ID]) as schemaname,object_name(parent_object_id) as parentname
from sys.objects where [type] = 'F'

OPEN fk_cursor

FETCH NEXT FROM fk_cursor
INTO @fk_name, @schema_name, @parent_name

WHILE @@FETCH_STATUS = 0
BEGIN
        SET @sql = N''
        SET @sql = N'ALTER TABLE [' + @schema_name + N'].[' + @parent_name + N'] DROP ' + @fk_name
        PRINT @sql
        FETCH NEXT FROM fk_cursor INTO @fk_name, @schema_name, @parent_name
END

CLOSE fk_cursor
DEALLOCATE fk_cursor


-- DROP user tables
SET NOCOUNT ON
DECLARE @tbl_name nvarchar(500)


DECLARE tbl_cursor CURSOR FOR
SELECT schema_name([SCHEMA_ID]) as schemaname, [name]
from sys.objects where [type] = 'U' order by [name]


OPEN tbl_cursor

FETCH NEXT FROM tbl_cursor
INTO @schema_name, @tbl_name

WHILE @@FETCH_STATUS = 0
BEGIN
        SET @sql = N'DROP TABLE [' + @schema_name + '].[' + @tbl_name + N'] '
        PRINT @sql    
        FETCH NEXT FROM tbl_cursor INTO @schema_name, @tbl_name

END

CLOSE tbl_cursor
DEALLOCATE tbl_cursor

Caution: Please peruse the script before executing it! Please refer to my blog post on this: http://zen-and-art-of-programming.blogspot.in/2015/04/cleanup-restore-master-database-in-sql.html

Answer 3

With schema name ;

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(Schema_name(schema_id)) + '.' + Quotename(Name)+ CHAR(10) + 'GO' + CHAR(10)
FROM sys.objects
WHERE is_ms_shipped <> 1
AND TYPE IN ('U','P','FN','V')