Drop all procedures not shipped with MS SQL Server

Sometimes it is necessary to delete all procedures from a database. But we do not want to delete system stored procedure. The following sql statement accomplish exactly what we need.

PRINT 'delete all stored procedures not shipped via MS SQL Server itself'
DECLARE @prc varchar(255) 
WHILE  (
	SELECT Count(*) from sys.procedures p
	INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
	WHERE p.type = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
       ) > 0
BEGIN 
  SET @prc = (
        SELECT TOP 1 p.Name from sys.procedures p
        INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
        WHERE p.type = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
        ) 
  SET @prc = 'DROP PROCEDURE ' + @prc 
  PRINT @prc
  EXEC(@prc) 
END

Comments

There were no comments found for this weblog.

Du mußt Dich einloggen um diesen Weblog kommentieren zu können!

Related Snippets

Snippet Tags