19
DecDrop all tables, stored procedure, views and triggers
31 Aug 2022
Intermediate
200K Views
1 min read
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
Read More Articles Related to SQL Server
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.
Take our free skill tests to evaluate your skill!
In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.
