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

Hello,

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

Thanks

Zaki
zaki100Asked:
Who is Participating?
 
Rajkumar GsSoftware EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.