CameTooFar

A Nerd's False Positive.

Sql Server – Drop all tables and stored procedure


For last few days, I’s makings come changes to one of the website that I have hosted recently. Since, it’s dynamic website, yes, it contains dozens of tables and stored procedures. As part of testing, I find it difficult to delete the tables and stored procedure hosted in my server. Simply, because the service providers Dbase UI does not allow you to drop a database as a whole. So, every time, I have to manually delete all the tables and stored procedure which is painful as expected. Every time, I delete an object (table/stored procedure), my object explorer refreshes and takes time to reflect the changes.

In pain, I ringed one of my T-SQL buddy and he shared a script with me. Now, that simple!

Run this script, all your tables and stored procedures will be dropped at once.

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

Hope this helped.

Thanks!