2,596
社区成员
发帖
与我相关
我的任务
分享
create procedure [%QUALIFIER%]%PROC%
@objname varchar(100),
@condition_name varchar(1000),
@is_bak varchar(1)
as
declare @start_time datetime
declare @end_time datetime
declare @indid int
declare @keys varchar(1024)
declare @msg varchar(1024)
declare @create_tmp_str varchar(1024)
declare @lock_datarows_str varchar(100)
declare @pk_str varchar(1024)
declare @start_str varchar(100)
declare @default_str varchar(1024)
declare @index_str varchar(1024)
declare @grant_str varchar(1024)
declare @ddl_str varchar(1024)
declare @rename_str varchar(1024)
declare @end_str varchar(100)
declare @column_name varchar(1024)
declare @default_name varchar(1024)
declare @new_id char(32)
declare @data_str varchar(10)
begin
set nocount on
set @start_time=getdate()
set @data_str=right(convert(varchar(10),getdate(),112),4)
select @new_id =newid()
insert into dba_delete_history_data
select @new_id,@objname,@start_time,null,0
set @start_str =@objname+' data cleaning start......'
print @start_str
set @create_tmp_str='select * into '+@objname+'_tmp from '+@objname+' '+@condition_name
exec (@create_tmp_str)
if @@error != 0
return -1
set @lock_datarows_str='alter table '+@objname+'_tmp lock datarows'
exec (@lock_datarows_str)
create table #temp
(
id int identity,
column_name varchar(200),
default_name varchar(200)
)
declare @n int
declare @rows int
select @n=1
insert #temp(column_name,default_name)
select C.name,D.text
from syscomments D,sysprocedures P ,syscolumns C,sysobjects O
where D.id = C.cdefault and P.id=D.id and C.id=O.id and O.type='U' and O.name=@objname
and P.sequence=0 and P.status & 4096 =4096
select @rows = @@rowcount
while @n <= @rows
begin
select @default_str ='alter table '+@objname+'_tmp replace '+column_name+' '+default_name
from #temp where id = @n
exec (@default_str)
select @n = @n + 1
end
drop table #temp
if @@trancount = 0
begin set chained off end
set transaction isolation level 1
if @objname like "%.%.%" and substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
begin raiserror 17460 end
if not exists (select id from sysobjects where id = object_id(@objname))
begin raiserror 17461 end
select @indid = min(indid)
from sysindexes where id = object_id(@objname) and indid > 0 and indid < 255
if @indid is NULL
begin
exec sp_getmessage 17640, @msg output
print @msg
end
while @indid is not NULL
begin
declare @i int
declare @thiskey varchar(30)
declare @sorder char(4)
declare @lastindid int
select @keys = "", @i = 1
while @i <= 31
begin
select @thiskey = index_col(@objname, @indid, @i)
if (@thiskey is NULL)
begin
goto keysdone
end
if @i > 1
begin
select @keys = @keys + ", "
end
select @keys = @keys + @thiskey
select @sorder = index_colorder(@objname, @indid, @i)
if (@sorder = "DESC")
select @keys = @keys + " " + @sorder
select @i = @i + 1
end
keysdone:
print '-----------------------------------------------------'
if exists(select 1 from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 = 2048)
begin
select @pk_str='alter table '+@objname+'_tmp add constraint '+name+' primary key nonclustered ('+@keys+')'
from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 = 2048
exec (@pk_str)
end
if exists(select 1 from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 <> 2048)
begin
select @index_str='create index '+name+' on '+@objname+'_tmp('+@keys+')'
from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 <> 2048
exec (@index_str)
end
select @lastindid = @indid
select @indid = NULL
select @indid = min(indid) from sysindexes where id = object_id(@objname) and indid > @lastindid and indid < 255
end
set @grant_str='grant select,insert,delete,update on '+@objname+'_tmp to web'
exec (@grant_str)
set @grant_str='grant select,insert,delete,update on '+@objname+'_tmp to sms'
exec (@grant_str)
if(lower(@is_bak)='y')
begin
set @rename_str='exec sp_rename '+@objname+','+@objname+'_'+@data_str
exec (@rename_str)
set @rename_str=''
end
if(lower(@is_bak)='n')
begin
set @ddl_str='truncate table '+@objname
exec (@ddl_str)
set @ddl_str='drop table '+@objname
exec (@ddl_str)
end
set @rename_str='exec sp_rename '+@objname+'_tmp,'+@objname
exec (@rename_str)
set @end_str=@objname+' data cleaning complete!'
print @end_str
set @end_time=getdate()
update dba_delete_history_data
set status=1,end_time=@end_time
where id=@new_id
set nocount off
end
go
sp_procxmode up_delete_history_table, anymode
go