How to drop or delete all tables, all user defined stored procedure, views and triggers from MSSQL Server 2008 with example

Category > MSSQLSERVER || Published on : Sunday, May 17, 2015 || Views: 5697 || MSSQL Server 2008 remove all tables and stored procedures from database remove all tables and data from database remove all triggers remove all views drop all triggers drop all views


Hi Guys, In previous articles you have learned how can we convert negative a number to positive number in SQL Server 2005/2008. , Get todays Day Name SQL server , In MS-SQL Server How to get tables columns with increment value with SQL Query Example , Delete all the rows from all the tables in Ms-SQL Server  and this tutorial, we are going to learn and understand How to drop or delete all tables, all user defined stored procedure, views and triggers from MSSQL Server 2008 with example

In some conditions, we have the requirement of remove all user defined tables, user defined stored procedure and tiggers from a particular database. Suppose your project having hundreds of tables, Stored Procedures, View and triggers and removing one by one will be a very time conuming task for developers. In this tutorial, I would like to exaplain and share the script which can drop or delete all tables, all user defined stored procedure, views and triggers from MSSQL Server 2008

Step 1: Start or Run your MSSQL Server Management Studio 2008 R2

Step 2: Right click on the database on which you want to work and click on "New Query" menu item.

Step 3: Write the following SQL Query to Delete or Remove all user defined tables

 -- Delete or Remove all user defined tables
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" 

Step 4: If you want to delete all user defined Stored Procedure then write the following SQL Query

-- delete or drop  all user defined Stored Procedure 
Declare @pName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'p' 
Open cur 
Fetch Next From cur Into @pName 
While @@fetch_status = 0 
Begin 
 Exec('drop procedure ' + @pName ) 
 Fetch Next From cur Into @pName  
End
Close cur 
Deallocate cur 


Step 5: If you want to delete or remove all user defined Views then write the following SQL Query

-- drop all user defined views
Declare @vName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'v' 
Open cur 
Fetch Next From cur Into @vName 
While @@fetch_status = 0 
Begin 
 Exec('drop view ' + @vName) 
 Fetch Next From cur Into @vName 
End
Close cur 
Deallocate cur 

Step 6: If you want to delete or remove all user defined Triggers then write the following SQL Query

 -- drop all user defined triggers
Declare @tName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'tr' 
Open cur 
Fetch Next From cur Into @tName 
While @@fetch_status = 0 
Begin 
 Exec('drop trigger ' + @tName) 
 Fetch Next From cur Into @tName 
End
Close cur 
Deallocate cur 

Conclusion:
Hope you find this tutorial useful and we have learned How to drop or delete all tables, all user defined stored procedure, views and triggers from MSSQL Server 2008 with example