Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-11-21
1
Medium Priority
?
526 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

618 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