SQL Query To Drop or Delete All Table, View, Stored Procedure and Trigger

In this article I will show you how you can drop the entire tables, views, stored procedure and triggers in sql server using a single query.

Some of my previous articles are as follows: Insert, Update, Delete, Select, Drop and Create Table In Ms Sql Server Database Table, How to Add Email Id Hyperlink or Mailto Link in Asp.net MVC WebGrid, How to reset your lost sql sa password | Reset SQL Server sa password by Windows Authentication.

In this article first I will show you how you can drop or delete all tables, views, stored procedure and triggers from your sql server database.

Drip All SQL Tables:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Drop All SQL View:

DECLARE @view_name varchar(100) DECLARE sqlview_cursor CURSOR     FOR select table_name from INFORMATION_SCHEMA.views OPEN sqlview_cursor FETCH NEXT FROM sqlview_cursor INTO @view_name; WHILE @@FETCH_STATUS = 0 BEGIN   declare @sql varchar(100)   set @sql='DROP VIEW '+@view_name ;   exec(@sql);   FETCH NEXT FROM sqlview_cursor INTO @view_name; END CLOSE sqlview_cursor; DEALLOCATE sqlview_cursor;

Drop all SQL Triggers:

DECLARE @trigger_name varchar(100) DECLARE sqltrigger_cursor CURSOR     FOR select objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled') OPEN sqltrigger_cursor FETCH NEXT FROM sqltrigger_cursor INTO @trigger_name; WHILE @@FETCH_STATUS = 0 BEGIN   declare @sql varchar(100)   set @sql='DROP VIEW '+@trigger_name ;   exec(@sql);   FETCH NEXT FROM sqltrigger_cursor INTO @trigger_name; END CLOSE sqltrigger_cursor; DEALLOCATE sqltrigger_cursor;

I think this will help you. Please let me know your comments.