Category >
MSSQLSERVER
|| Published on :
Sunday, May 17, 2015 || Views:
5832
||
MSSQL Server 2008 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
Hi Guys, In previous articles you have learned how can we convert negative a number to positive number in SQL Server 2005/2008. , Get todays Day Name SQL server , In MS-SQL Server How to get tables columns with increment value with SQL Query Example , Delete all the rows from all the tables in Ms-SQL Server and this tutorial, we are going to learn and understand How to drop or delete all tables, all user defined stored procedure, views and triggers from MSSQL Server 2008 with example
In some conditions, we have the requirement of remove all user defined tables, user defined stored procedure and tiggers from a particular database. Suppose your project having hundreds of tables, Stored Procedures, View and triggers and removing one by one will be a very time conuming task for developers. In this tutorial, I would like to exaplain and share the script which can drop or delete all tables, all user defined stored procedure, views and triggers from MSSQL Server 2008
Step 1: Start or Run your MSSQL Server Management Studio 2008 R2
Step 2: Right click on the database on which you want to work and click on "New Query" menu item.
Step 3: Write the following SQL Query to Delete or Remove all user defined tables
-- Delete or Remove all user defined tables
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
Step 4: If you want to delete all user defined Stored Procedure then write the following SQL Query
-- delete or drop all user defined Stored Procedure
Declare @pName varchar(500)
Declare cur Cursor For Select [name] From sys.objects where type = 'p'
Open cur
Fetch Next From cur Into @pName
While @@fetch_status = 0
Begin
Exec('drop procedure ' + @pName )
Fetch Next From cur Into @pName
End
Close cur
Deallocate cur
Step 5: If you want to delete or remove all user defined Views then write the following SQL Query
-- drop all user defined views
Declare @vName varchar(500)
Declare cur Cursor For Select [name] From sys.objects where type = 'v'
Open cur
Fetch Next From cur Into @vName
While @@fetch_status = 0
Begin
Exec('drop view ' + @vName)
Fetch Next From cur Into @vName
End
Close cur
Deallocate cur
Step 6: If you want to delete or remove all user defined Triggers then write the following SQL Query
-- drop all user defined triggers
Declare @tName varchar(500)
Declare cur Cursor For Select [name] From sys.objects where type = 'tr'
Open cur
Fetch Next From cur Into @tName
While @@fetch_status = 0
Begin
Exec('drop trigger ' + @tName)
Fetch Next From cur Into @tName
End
Close cur
Deallocate cur
Conclusion:
Hope you find this tutorial useful and we have learned How to drop or delete all tables, all user defined stored procedure, views and triggers from MSSQL Server 2008 with example