批量删除存储过程

DECLARE @STRING VARCHAR(8000)
WHILE EXISTS(SELECT * FROM sys.all_objects WHERE type = 'p' and name like 'Organ%')
BEGIN
 SELECT @STRING='DROP  PROCEDURE '+b.name+'.'+a.name  FROM   sys.all_objects a
 left join sys.schemas b on a.schema_id = b.schema_id
WHERE   a.type = 'P' AND a.name like 'Organ%'
 --SELECT @STRING
 EXEC(@STRING)
END

GO

SELECT * FROM sys.all_objects WHERE type = 'p' and name like 'Organ%'
select * from sys.schemas

 

其中'Organ%'指存储过程名称包含“Organ”关键字

转自http://www.cnblogs.com/wonechao/archive/2009/08/26/1554392.html