Please enable Javascript to correctly display the contents on Dot Net Tricks!

Drop all tables, stored procedure, views and triggers

  Posted By : Shailendra Chauhan, 27 Sep 2012
Updated On : 27 Sep 2012
Total Views : 140,491   
Support : SQL Server 2005,08,12
 

Sometimes, there is a case, when we need to remove all tables, stored procedure, views and triggers completely from the database. If you have around 100 tables, stored procedure and views in your database, to remove these, completely from database became a tedious task. In this article, I would like to share the script by which you can remove tables, stored procedure, views and triggers completely from database.

Remove all Tables

 -- drop all user defined tables
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" 

Remove all User-defined Stored Procedures

 -- 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 
What do you think?

I hope you will enjoy these tricks while working with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

 
You might like
 
21 OCT
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM-09:00 AM IST

Know More
14 OCT
MEAN Stack Development (online)

MON-FRI 06:00 AM -07:30 AM IST

Know More
4 OCT
NodeJS Development (online)

MON-FRI 08:00 PM - 09:30 PM IST

Know More
4 OCT
MEAN Stack Development (online)

MON-FRI 08:00 PM-09:30 PM IST

Know More
3 OCT
ASP.NET MVC with AngularJS Development (online)

MON-FRI 09:30 PM-11:00 PM IST

Know More
1 OCT
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

17 SEP
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     05:00 PM-06:30 PM IST

17 SEP
ASP.NET MVC with AngularJS Development (online)

Sat, Sun     03:00 PM-05:00 PM IST

17 SEP
PPC Marketing (offline)

Sat, Sun     03:00 PM-05:00 PM IST

3 SEP
NodeJS Development (offline)

Sat, Sun     11:00 AM-12:30 PM IST

24 JUL
ASP.NET MVC with AngularJS Development (offline)

Sat, Sun     09:30 AM-11:00 AM

BROWSE BY CATEGORY
 
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+