我有一组以“somename_%”命名的存储过程。是否有一种方法可以使用一个查询来删除这些存储过程,例如:
DROP PROCEDURE where name like 'somename_%'
。
我有一组以“somename_%”命名的存储过程。是否有一种方法可以使用一个查询来删除这些存储过程,例如:
DROP PROCEDURE where name like 'somename_%'
。
这适用于 MSSQL 2005 及以上版本。
DECLARE @DropScript varchar(max)
set @DropScript = ''
SELECT @DropScript = @DropScript + 'DROP PROCEDURE [' + schema_name(schema_id)+ '].' + '[' + name + ']
' FROM sys.procedures
where name like 'somename_%'
exec (@DropScript)
你可以通过查询数据字典来生成DDL。例如,在Oracle中:
SELECT 'DROP PROCEDURE "'||owner||'"."'||object_name||'";'
FROM all_procedures
WHERE procedure_name IS NULL
AND lower(object_name) LIKE 'somename_%';
BEGIN TRANSACTION;
GO
CREATE PROC Test_1 AS
BEGIN;
PRINT '1'
END;
GO
CREATE PROC Test_2 AS
BEGIN;
PRINT '2'
END;
GO
SELECT * FROM sys.objects WHERE name LIKE 'Test%' AND TYPE = 'P';
DECLARE @Stmt NVARCHAR(MAX);
SET @Stmt = ( SELECT 'DROP PROC ' + STUFF (x.Stmt, 1, 2, SPACE(0))
FROM (SELECT ', ' + SCHEMA_NAME(Obj.Schema_ID) + CHAR(46) + Obj.Name
FROM sys.objects AS Obj
WHERE Obj.name LIKE 'Test%'
AND obj.TYPE = 'P'
FOR XML PATH ('')
) AS X (Stmt)
);
SELECT @Stmt;
EXEC sp_ExecuteSQL @Stmt;
SELECT * FROM sys.objects WHERE name LIKE 'Test%' AND TYPE = 'P';
ROLLBACK;
SELECT 'DROP PROCEDURE ' + procName FROM system_procedures_table WHERE procName like...
或者在 Excel 中创建。