|
发表于 2024-7-26 17:25:26
|
显示全部楼层
给你答案,清除后重新添加即可
--- 批量删除woolinte开头的表
CREATE PROCEDURE dbo.deletetable
AS
DECLARE @Name as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select name from sysobjects where type='U' and name like 'woolinte_%'
OPEN curObject
FETCH NEXT FROM curObject INTO @Name
WHILE(@@FETCH_STATUS=0)
BEGIN
--print 'drop table '+@Name
exec('drop table '+@Name)
FETCH NEXT FROM curObject INTO @Name
END
close curObject
deallocate curObject
GO
EXEC deletetable
DROP PROCEDURE deletetable
if EXISTS(select * from sysobjects where id = OBJECT_ID(N'[PR_WoolinteRestoreProcedureTemp]') and OBJECTPROPERTY(id,N'IsProcedure')= 1)
DROP PROCEDURE PR_WoolinteRestoreProcedureTemp;
Go
CREATE procedure [dbo].[PR_WoolinteRestoreProcedureTemp](@CleanType varchar(100)) AS
Declare @SqlWhere varchar(8000);
begin tran
---Print @SqlWhere
--------==========================================
DECLARE @MyTableName1 VARCHAR(200),@MyTableName2 VARCHAR(200),@MyTableName3 VARCHAR(200);
DECLARE @MyProcedureName1 VARCHAR(200),@MyProcedureName2 VARCHAR(200),@MyProcedureName3 VARCHAR(200);
DECLARE @MyFunctionName VARCHAR(200);
DECLARE @MyProcedureName6 VARCHAR(200), @MyProcedureName7 VARCHAR(200), @MyProcedureName8 VARCHAR(200);
DECLARE @MyProcedureName9 VARCHAR(200), @MyProcedureName10 VARCHAR(200), @MyProcedureName11 VARCHAR(200);
DECLARE @MyProcedureName12 VARCHAR(200), @MyProcedureName13 VARCHAR(200), @MyProcedureName14 VARCHAR(200);
DECLARE @MyProcedureName15 VARCHAR(200), @MyProcedureName16 VARCHAR(200), @MyProcedureName17 VARCHAR(200), @MyProcedureName18 VARCHAR(200), @MyProcedureName19 VARCHAR(1000),@MyProcedureName20 VARCHAR(200);
DECLARE @MyProcedureName21 VARCHAR(200), @MyProcedureName22 VARCHAR(200), @MyProcedureName23 VARCHAR(200), @MyProcedureName24 VARCHAR(200), @MyProcedureName25 VARCHAR(200);
--Set @MyTableName3='%Wlt_%';
Set @MyProcedureName1='PR_Media%';
Set @MyProcedureName2='%PR_Woolinte%';
SET @MyProcedureName3='Woolinte_SetBaseRight';
Set @MyFunctionName='%FN_Woolinte%';
set @MyProcedureName6='WLT_FZCopyFromBak'
set @MyProcedureName7='WLT_PosDraftToDlyBill'
set @MyProcedureName8='FZBS_FZDeleteBill'
set @MyProcedureName9='WTL_DlyAccount'
set @MyProcedureName10='pr_woolinteFZGetBillCourse_CS'
set @MyProcedureName11 = 'FZBS_FZCopyFromBak';
set @MyProcedureName12= 'FZBS_FZDeleteBill';
set @MyProcedureName13 = 'FZBS_PosDraftToDlyBill';
set @MyProcedureName14= 'FZBS_PosToDraftBill'
set @MyProcedureName15 = 'FZBS_PosToOrderBill';
set @MyProcedureName16 = 'WLT_DistributionYS';
set @MyProcedureName17 = 'Pos_BIllACCount';
set @MyProcedureName18 = 'Woolinte_VirtualStockStatus';
set @MyProcedureName19 = 'FNGetBill';
SET @MyProcedureName20 ='FN_UpLoadImageGroup';
SET @MyProcedureName21 ='%Woolinte_Dealers%';
SET @MyProcedureName22 ='PR_Audit%';
SET @MyProcedureName23 ='PR_CMAuditing';
SET @MyProcedureName24 ='PR_CMQuery';
SET @MyProcedureName25 ='PR_CMUnAuditing';
--------==========================================
---读取自定义函数列表 删除函数
--------==========================================
DECLARE @FunctionName VARCHAR(5000),@FunctionSQL VARCHAR(5000);
Declare FunctionList cursor for
Select [name] From sysobjects where
xtype='FN' and ([name] Like @MyFunctionName OR [name]=@MyProcedureName20 OR [name]='PR_Woolintef_getnums');
--------==========================================
Open FunctionList
FETCH NEXT From FunctionList INTO @FunctionName
WHILE @@FETCH_STATUS = 0
BEGIN
----赋值SQL语句
Set @FunctionSQL=' Drop Function '+ @FunctionName + Char(13);
----执行SQL语句
EXEC (@FunctionSQL);
if(@@ERROR<>0)
goto Error1
FETCH NEXT From FunctionList INTO @FunctionName
END
CLOSE FunctionList
DEALLOCATE FunctionList;
--------==========================================
---读取存储过程名称列表 删除存储过程
--------==========================================
DECLARE @ProcedureName VARCHAR(5000),@ExecSQL VARCHAR(5000);
Declare ProcedureList cursor for
Select [name] From sysobjects where
xtype='P' and [name] <>'PR_WoolinteRestoreProcedure' and ([name] Like @MyProcedureName1 Or [name] Like @MyProcedureName2 OR [name] =@MyProcedureName3
OR [name] =@MyProcedureName6 OR [name] =@MyProcedureName7 OR [name] =@MyProcedureName8 OR [name] =@MyProcedureName9 OR [name] =@MyProcedureName10
OR [name] =@MyProcedureName11 OR [name] =@MyProcedureName12 OR [name] =@MyProcedureName13 OR [name] =@MyProcedureName14
OR [name] =@MyProcedureName15 OR [name] =@MyProcedureName16 OR [name] =@MyProcedureName17 OR [name] =@MyProcedureName18 OR [name] =@MyProcedureName19 OR [name] LIKE @MyProcedureName21
OR [name] LIKE @MyProcedureName22 OR [name]=@MyProcedureName23 OR [name]=@MyProcedureName24 OR [name]=@MyProcedureName25
OR [name] ='pr_MonthSaleInfo' OR [name] ='pr_TodaySaleInfo' OR [name] ='Woolinte_GetOnePFenBu' OR [name] ='pr_TodaySale');
--------==========================================
Open ProcedureList
FETCH NEXT From ProcedureList INTO @ProcedureName
WHILE @@FETCH_STATUS = 0
BEGIN
----赋值SQL语句
Set @ExecSQL=' Drop Procedure '+ @ProcedureName + Char(13);
----执行SQL语句
EXEC (@ExecSQL);
if(@@ERROR<>0)
goto Error1
FETCH NEXT From ProcedureList INTO @ProcedureName
END
CLOSE ProcedureList
DEALLOCATE ProcedureList;
--------==========================================
commit tran
return ;
Error1:
rollback tran
GO
EXEC PR_WoolinteRestoreProcedureTemp ''
GO
|
|