How to remove user-defined objects from the main database

Question

I am using SQL Server 2008 R2 .

I created SQL Script for my project database, i.e. used to create all tables, constraints, views, stored procedures and functions with some minimal data to create a new database.

But by mistake, I ran it in the master database. So that all these materials are created in the master database.

Now I want to drop all that user-defined objects from the master database.

Is there an easy way to do this?

Answer 1

I just created this Script to create a Script to delete all User objects in your main database, just test it on the Dev server before executing it on the 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 + Name + CHAR(10) + 'GO' + CHAR(10) FROM Master.sys.objects WHERE is_ms_shipped <> 1 AND TYPE IN ('U','P','FN','V') -- Results to Text -- 

Create Script

 DROP TABLE Test_table1 GO DROP PROCEDURE usp_Test_Proc1 GO DROP VIEW vw_TestView_1 GO DROP PROCEDURE usp_Test_Proc2 GO DROP FUNCTION udf_Test_Function_GetList GO 

Note

If the generated Script tries to delete the table referenced by another table through a foreign key, it throws an error.

Answer 2

With the name of the schema;

 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') 

Answer 3

Please refer to this link for scripts for constraints and tables with constraints. http://www.toadworld.com/platforms/sql-server/w/wiki/10407.delete-user-objects-from-a-database.aspx However, it does not work with the tables defined in the schema, and it demanded from me make 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: read through the script before performing this process! Please refer to my blog post about this: http://zen-and-art-of-programming.blogspot.in/2015/04/cleanup-restore-master-database-in-sql.html