?
Solved

How to delete all stored procedures and views in database with one click?

Posted on 2010-11-21
1
Medium Priority
?
525 Views
Last Modified: 2012-05-10
Hello,

I need a script in SQL 2005 to delete a all stored procedures and views in one database with one click?

Thanks

Zaki
0
Comment
Question by:zaki100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 2000 total points
ID: 34185599
To drop all stored Procedures
 
/* Drop all non-system stored procs */ 
DECLARE @name VARCHAR(128) 
DECLARE @SQL VARCHAR(254) 
 
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) 
 
WHILE @name is not null 
BEGIN 
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']' 
    EXEC (@SQL) 
    PRINT 'Dropped Procedure: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) 
END 
GO

Open in new window


To drop all views
 
/* Drop all views */ 
DECLARE @name VARCHAR(128) 
DECLARE @SQL VARCHAR(254) 
 
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) 
 
WHILE @name IS NOT NULL 
BEGIN 
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']' 
    EXEC (@SQL) 
    PRINT 'Dropped View: ' + @name 
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) 
END 
GO

Open in new window


http://stackoverflow.com/questions/536350/sql-server-2005-drop-all-the-tables-stored-procedures-triggers-constriants-an

Hope this helps
Raj
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question