Thay vì phải xóa từng thủ tục 1, thì đây là cách hay để giải quyết khó khăn mà chúng ta thường mắc phải trong việc sử dụng Database
1. Xóa store procedures
Create Procedure dbo.DeleteAllProcedures
As
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
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
Go
Grant Execute On dbo.DeleteAllProcedures To Public
Go2. Xóa store procedures + views + functions
USE [db_name]
CREATE PROCEDURE dbo.__DeleteAllProcedures
As
declare @procName varchar(500)
-- Removes stored procedures
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
if @procName <> '__DeleteAllProcedures'
exec('drop procedure ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
-- Removes Views
declare cur cursor
for select [name] from sys.objects where [type] = 'v'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop view ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
-- Removes Functions
declare cur cursor
for select [name] from sys.objects WHERE [type] = 'fn'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop function ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
-- removes itselfs
DROP PROCEDURE __DeleteAllProcedures
Go
exec
__DeleteAllProcedures