Article

Drop all Tables, Stored Procedures, Views And Triggers with one SQL query in SQL Server.

15 Dec 2016 Kamal Pratap
0 Comments 1170 Views



Here we explain how to drop all Tables, Stored Procedures, Views And Triggers in one SQL statement. Suppose we have lot of tables, procedure, views & triggers we need to remove them from the database will be a difficult task.

In this article we use sys.objects to find the list of objects and generate script for drop the object.

--Remove all Tables
 
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" 
--Remove 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

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

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 

 

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment