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,135   
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.

 
Recommended for you
 
About the Author
Shailendra Chauhan

Shailendra Chauhan is an Entrepreneur, Author, Architect, and Corporate Trainer. He has rewarded as Microsoft MVP for his exceptional contributions in Microsoft Visual Studio and Development Technologies.

With more than 7 years in hand experience Shailendra Chauhan is a polymath in the domains of Microsoft .NET technologies and an array of other technologies including JavaScript, AngularJS, Node.js, Ionic and NoSQL Databases to name but a few.

He is the author of some of most popular e-books which encompass technical Interview on Node.js Interview Questions and Answers , ASP.NET MVC Interview Questions and Answers , AngularJS Interview Questions and Answers and LINQ Interview Questions and Answers. Furthermore he is a technical reviewer for book on ASP.NET MVC 4 Mobile App Development. Know more...
 
Free Interview Books
 
28 JUL
NodeJS Development (online)

Thursday 06:00 AM-7:30 AM IST

More Details
25 JUL
ASP.NET MVC with AngularJS Development (online)

Mon-Fri     (07:30 AM-09:00 AM IST)

25 JUL
AngularJS Development (online)

Mon-Fri     09:00 PM-10:30 PM IST

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

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

24 JUL
AngularJS Development (offline)

Sat,Sun     (08:00 AM-09:30 AM)

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

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

27 JUN
ASP.NET MVC with AngularJS Development (online)

Mon - Fri     (07:00 AM-09:00 AM IST)

BROWSE BY CATEGORY
 
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 
+