Loading, please wait...

How to drop all tables, stored procedure, view and triggers from database

Here I will explain how to drop/delete all tables, stored procedure and triggers from SQL server by single SQL query

Introduction:

In this article, I have explained how to drop all tables, stored procedures, views and triggers from the database. This task is difficult when we have so much quantity of tables, stored procedures and views in the database.  In this article, I have shared script to delete all tables, stored procedure and views from the database with the single command.

 

Remove all Tables:

- drop all user defined tables

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

 

Remove all user-defined stored procedure

-- drop all user defined stored procedures

Declare @procName varchar(500)

Declare cur Cursor For Select [name] From sys.objects where type = 'p'

Open cur

Fetch Next From cur Into @procName

While @@fetch_status = 0

Begin

 Exec('drop procedure ' + @procName)

 Fetch Next From cur Into @procName

End

Close cur

Deallocate cur

 

Remove all Views:

-- drop all user defined views

Declare @viewName varchar(500)

Declare cur Cursor For Select [name] From sys.objects where type = 'v'

Open cur

Fetch Next From cur Into @viewName

While @@fetch_status = 0

Begin

 Exec('drop view ' + @viewName)

 Fetch Next From cur Into @viewName

End

Close cur

Deallocate cur

 

Remove all triggers

-- drop all user defined triggers

Declare @trgName varchar(500)

Declare cur Cursor For Select [name] From sys.objects where type = 'tr'

Open cur

Fetch Next From cur Into @trgName

While @@fetch_status = 0

Begin

 Exec('drop trigger ' + @trgName)

 Fetch Next From cur Into @trgName

End

Close cur

Deallocate cur

 

If you have any questions or problem reply it into the command box.

 Like this post? Don’t forget to share it!