Solved

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

Posted on 2010-11-21
1
518 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
1 Comment
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now