SQL Server 데이터베이스에서 모든 저장 프로시저를 한 번에 삭제하는 방법은 무엇입니까?

SQL Server 데이터베이스에서 모든 저장 프로시저를 한 번에 삭제하는 방법은 무엇입니까?

현재 스크립트 파일에 저장된 각 프로시저에 대해 별도의 드롭 문을 사용합니다.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MySP]')   
  AND type in (N'P', N'PC'))  
DROP PROCEDURE [dbo].[MySP] 

한 번에 다 떨어뜨릴 수 있는 방법이 있을까요, 아니면 순환해서 떨어뜨릴 수 있을까요?

다음과 같은 방식으로 진행하고 싶습니다.

• 먼저 시스템 카탈로그 보기를 검사하여 삭제할 저장 프로시저 목록을 생성합니다.

  SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + '];'
  FROM sys.procedures p 
  

  다음 목록이 생성됩니다.DROP PROCEDURESSMS 출력 창에 문이 표시됩니다.

• 목록을 새 쿼리 창에 복사하고 수정/변경한 다음 실행

지저분하고 느린 커서가 없으므로 실제로 삭제하기 전에 삭제할 절차 목록을 확인하고 다시 확인할 수 있습니다.

(SQL Server의 저장 프로시저를 사용하여 데이터베이스에서 모든 프로시저 삭제에서 발견됨)와 같은 것입니다.

그건 그렇고, 이건 매우 위험한 일인 것 같아요, 그냥 생각해보면...

declare @procName varchar(500)
declare cur cursor 

for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    exec('drop procedure [' + @procName + ']')
    fetch next from cur into @procName
end
close cur
deallocate cur

1. 개체 탐색기 창에서 저장 프로시저 폴더를 선택합니다.
2. F7을 누릅니다(또는 메인 메뉴에서 View > Object Explorer Details를 선택합니다).
3. 시스템 테이블을 제외한 모든 절차를 선택합니다.
4. Delete 버튼을 누르고 OK를 선택합니다.

동일한 방법으로 표 또는 보기를 삭제할 수 있습니다.

db(dbu에서 sp를 삭제하려는 경우)에 아래 저장 프로시저를 만듭니다.

그런 다음 해당 절차를 마우스 오른쪽 버튼으로 클릭하고 저장 프로시저 실행을 클릭합니다.

그런 다음 확인을 클릭합니다.

create Procedure [dbo].[DeleteAllProcedures]
As 
declare @schemaName varchar(500)    
declare @procName varchar(500)
declare cur cursor
for select s.Name, 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%'
ORDER BY s.Name, p.Name
open cur

fetch next from cur into @schemaName,@procName
while @@fetch_status = 0
begin
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @schemaName + '.' + @procName)
fetch next from cur into @schemaName,@procName
end
close cur
deallocate cur

저는 이것이 가장 간단한 방법이라고 생각합니다.

DECLARE @sql VARCHAR(MAX)='';

SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects 
WHERE type = 'p' AND  is_ms_shipped = 0

exec(@sql);

데이터베이스에 저장된 모든 프로시저에 대한 drop 문을 가져오려면 'DROP PROCEDURE' + ' + F.NAME + ';' FROM SYS.objects ASF(여기서 =' 입력)를 선택합니다.P'

DECLARE @sql VARCHAR(MAX)
SET @sql=''
SELECT @sql=@sql+'drop procedure ['+name +'];' FROM sys.objects
WHERE type = 'p' AND  is_ms_shipped = 0
exec(@sql);

이것 좀 먹어봐요, 저한테 효과가 있어요.

DECLARE @spname sysname;
DECLARE SPCursor CURSOR FOR
SELECT SCHEMA_NAME(schema_id) + '.' + name
FROM sys.objects
WHERE type = 'P';
OPEN SPCursor;
FETCH NEXT FROM SPCursor INTO @spname;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DROP PROCEDURE ' + @spname);
FETCH NEXT FROM SPCursor INTO @spname;
END
CLOSE SPCursor;
DEALLOCATE SPCursor;

DECLARE @DeleteProcCommand NVARCHAR(500)

DECLARE Syntax_Cursor CURSOR
FOR
SELECT 'DROP PROCEDURE ' + p.NAME
FROM sys.procedures p

OPEN Syntax_Cursor

FETCH NEXT FROM Syntax_Cursor

INTO @DeleteProcCommand

WHILE (@@FETCH_STATUS = 0)
BEGIN

EXEC (@DeleteProcCommand)

FETCH NEXT FROM Syntax_Cursor
INTO @DeleteProcCommand

END

CLOSE Syntax_Cursor

DEALLOCATE Syntax_Cursor

사용해 보십시오.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'DROP PROCEDURE dbo.'
  + QUOTENAME(name) + ';
' FROM sys.procedures
WHERE name LIKE N'spname%'
AND SCHEMA_NAME(schema_id) = N'dbo';

EXEC sp_executesql @sql;

ANSI 호환, 커서 없음

DECLARE @SQL national character varying(MAX) 
SET @SQL= ''

SELECT @SQL= @SQL+ N'DROP PROCEDURE "' + REPLACE(SPECIFIC_SCHEMA, N'"', N'""') + N'"."' + REPLACE(SPECIFIC_NAME, N'"', N'""') + N'"; '
FROM INFORMATION_SCHEMA.ROUTINES 

WHERE (1=1) 
AND ROUTINE_TYPE = 'PROCEDURE' 
AND ROUTINE_NAME NOT IN 
(
     'dt_adduserobject'
    ,'dt_droppropertiesbyid'
    ,'dt_dropuserobjectbyid'
    ,'dt_generateansiname'
    ,'dt_getobjwithprop'
    ,'dt_getobjwithprop_u'
    ,'dt_getpropertiesbyid'
    ,'dt_getpropertiesbyid_u'
    ,'dt_setpropertybyid'
    ,'dt_setpropertybyid_u'
    ,'dt_verstamp006'
    ,'dt_verstamp007'

    ,'sp_helpdiagrams'
    ,'sp_creatediagram'
    ,'sp_alterdiagram'
    ,'sp_renamediagram'
    ,'sp_dropdiagram'

    ,'sp_helpdiagramdefinition'
    ,'fn_diagramobjects'
    ,'sp_upgraddiagrams'
) 


ORDER BY SPECIFIC_NAME 


-- PRINT @SQL
EXEC(@SQL) 

커서 없음, 비안시 호환:

DECLARE @sql NVARCHAR(MAX) = N''
, @lineFeed NVARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sql = @sql + N'DROP PROCEDURE ' + QUOTENAME(SPECIFIC_SCHEMA) + N'.' + QUOTENAME(SPECIFIC_NAME) + N';' + @lineFeed
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE' 
-- AND SPECIFIC_NAME LIKE 'sp[_]RPT[_]%'


AND ROUTINE_NAME NOT IN 
( 
    SELECT name FROM sys.procedures WHERE is_ms_shipped <> 0 
) 


ORDER BY SPECIFIC_NAME 


-- PRINT @sql 
EXECUTE(@sql)

커서와 시스템 절차를 혼합하면 다음과 같이 최적화된 솔루션을 얻을 수 있습니다.

DECLARE DelAllProcedures CURSOR
FOR
    SELECT name AS procedure_name 
    FROM sys.procedures;
OPEN DelAllProcedures
DECLARE @ProcName VARCHAR(100)
FETCH NEXT 
FROM DelAllProcedures
INTO @ProcName
WHILE @@FETCH_STATUS!=-1
BEGIN 
    DECLARE @command VARCHAR(100)
    SET @command=''
    SET @command=@command+'DROP PROCEDURE '+@ProcName
    --DROP PROCEDURE  @ProcName
    EXECUTE (@command)
    FETCH NEXT 
    FROM DelAllProcedures
    INTO @ProcName
END
CLOSE DelAllProcedures
DEALLOCATE DelAllProcedures

ANSI 호환, 커서 없음

PRINT ('1.a. Delete stored procedures ' + CONVERT( VARCHAR(19), GETDATE(), 121));
GO
DECLARE @procedure NVARCHAR(max)
DECLARE @n CHAR(1)
SET @n = CHAR(10)
SELECT @procedure = isnull( @procedure + @n, '' ) +
'DROP PROCEDURE [' + schema_name(schema_id) + '].[' + name + ']'
FROM sys.procedures

EXEC sp_executesql @procedure
PRINT ('1.b. Stored procedures deleted ' + CONVERT( VARCHAR(19), GETDATE(), 121));
GO

사용해 보십시오.

declare @procName varchar(500)
declare cur cursor 

for SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'
FROM sys.procedures p 
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    exec( @procName )
    fetch next from cur into @procName
end
close cur
deallocate cur

언급URL : https://stackoverflow.com/questions/2610820/how-to-drop-all-stored-procedures-at-once-in-sql-server-database