Your browser does not support JavaScript! Please enable script of your browser.
 
Coding is Rhyme
Handy Tricks For Beginners & Professionals
SQL Server 2012, 2008 - Tutorial, Article, Archive, Pdf, Handy Tricks, Training, Code Snippets, Reference Manual, T-SQL, PL/SQL, Development, Administration, Performance Tuning, Backup, Recovery, Reporting, Analysis, Security, XML, CLR Objects

Drop all tables, stored procedure, views and triggers

Posted By : Shailendra Chauhan, 27 Sep 2012
Updated On : 27 Sep 2012
  Version Support : SQL Server 2005,08,12
Keywords : remove all tables and stored procedures from database, remove all tables and data from database,remove all triggers, remove all views, drop all triggers, drop all views

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.

About the Author
Shailendra Chauhan works as Sr. Software Engineer at reputed MNC and has more than 5 years of hand over Microsoft .NET technologies. He is a .NET Consultant and is the founder & chief editor of www.dotnet-tricks.com and www.dotnetinterviewtricks.com blogs. He is an author of books ASP.NET MVC Interview Questions and Answers & LINQ Interview Questions and Answers.
He loves to work with web applications and mobile apps using Microsoft technology including ASP.NET, MVC, C#, SQL Server, WCF, Web API, Entity Framework,Cloud Computing, Windows Azure, jQuery, jQuery Mobile, Knockout.js, Angular.js and many more web technologies. More...
 
 
Recommended Articles!
 
 
Search Articles
 
 
Subscribe & follow Us
 
 
Protected by Copyscape Web Plagiarism Check
 
Free ASP.NET MVC Interview Book
Learn In Hindi
Browse By Category
 
Recent Articles
 
Popular Articles
 
Like us on Facebook
 
Featured AD
Join our Training programs in Delhi/Noida on ASP.NET MVC, WCF, EF, jQuery - call us    +91-9871749695