Subscribe to Subscribers

Online Members

baskar
More...

Resources » SQL Server

How to delete all stored procedures


Posted Date:     Category: SQL Server    
Author: Member Level: Gold    Points: 15


This a example to delete all stored procedures and delete all the tables from a database. Here cursor is used to fecth the consecutive objects and tables from the database. In this example you can also get an example Query for the cursor implementaition.



How to Delete all Stored Procedures


//declare a variable to store procedure name
declare @name varchar (100)
//declare cursor variable to get output from cursor
declare @cur cursor
set @cur = cursor for
select [name] from sys.objects where type = 'p'
//open cursor and fetch the value then drop the procedure
open @cur
fetch next from @cur into @name
while @@FETCH_STATUS = 0
begin
exec('drop proc '+ @name)
fetch next from @cur into @name
end
close @cur
deallocate @cur


How to Delete all Tables


//same as deleting stored procedure we can delete tables.
//declare a variable to table name
declare @tbl varchar (100)
//declare cursor variable to get output from cursor
declare @tabcur cursor
set @tabcur = CURSOR FOR
select [name] from sys.tables
//open cursor and fetch the value then drop the table
open @tabcur
fetch next from @tabcur into @tbl
while @@FETCH_STATUS = 0
begin
fetch next from @tabcur into @tbl
exec('drop table '+ @tbl)
end

close @tabcur
deallocate @tabcur





Did you like this resource? Share it with your friends and show your love!


Responses to "How to delete all stored procedures"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Next Resource: Debug your stored procedure
    Previous Resource: How to write a stored procedure in sql server
    Return to Resources
    Post New Resource
    Category: SQL Server


    Post resources and earn money!
     
    More Resources
    Popular Tags   Tag posting guidelines   Search Tags  
    How to Delete all Stored Procedures  .  How to Delete all Tables  .  

    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
    2005 - 2013 All Rights Reserved.
    .NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
    Articles, tutorials and all other content offered here is for educational purpose only.
    We are not associated with Microsoft or its partners.