Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

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
0
zaki100
Asked:
zaki100
1 Solution
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now