sybase 自动清理数据表的过程

之前发过一个询问贴：  http://topic.csdn.net/u/20110901/17/b84377e6-4ce0-4427-87f5-1f22c5539d95.html

后来通过不断的完善，形成了以下的过程 。

以下是2个过程 ，同时 为了记录清理的记录 特意加了一个清理的日志表。

主要作用是对大型的流水表进行数据清理。

原理是对表的数据进行在线重定义 然后对索引 默认值 约束进行重建

删除原表 将备份生成的新表改为原表 达到数据清理的目的。

IF EXISTS (SELECT 1 FROM sysobjects o WHERE  o.name = 'dba_delete_history_data'  AND o.type = 'U')
drop table dba_delete_history_data
go
create table dba_delete_history_data (
id                              char(32)                         not null  ,
table_name                      varchar(100)                     not null  ,
start_time                      datetime                             null  ,
end_time                        datetime                             null  ,
status                          int                              not null,   
default_fail_sql         varchar(7000)         null       
)
lock datarows
go


if exists (select 1
            from  sysobjects
            where id = object_id('up_delete_history_table')
            and   type = 'P')
   drop procedure up_delete_history_table
go


create procedure up_delete_history_table
@objname varchar(100),
@condition_name varchar(1000),
@is_bak varchar(1),
@server_lock_scheme varchar(50)
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 @drop_temp varchar(1024)
declare @lock_datarows_str varchar(100)
declare @pk_str varchar(1024)
declare @start_str varchar(100)
declare @default_str varchar(8000)
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 @pre_column_name varchar(1024)
declare @default_name varchar(255)
declare @new_id char(32)
declare @data_str varchar(10)
declare @sysstat2 int
declare @tab_lock_scheme varchar(50)


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 @drop_temp="if exists(select 1 from sysobjects where type='U' and name='"+@objname+"_tmp') begin truncate table "+@objname+"_tmp drop table "+@objname+"_tmp  end"
    exec (@drop_temp)
 
set @create_tmp_str='select * into '+@objname+'_tmp from '+@objname+'  '+@condition_name
exec (@create_tmp_str)
if  @@error  != 0
       return -1
select @sysstat2 = sysstat2 from sysobjects where name = @objname and type = 'U'
if @sysstat2 & 8192 = 8192    
     select @tab_lock_scheme = 'allpages'    
else if @sysstat2 & 16384 =  16384    
     select @tab_lock_scheme = 'datapages'    
else if @sysstat2 & 32768 = 32768    
     select @tab_lock_scheme = 'datarows'
if(@server_lock_scheme<>@tab_lock_scheme)
    begin
       set @lock_datarows_str='alter table '+@objname+'_tmp lock  ' +@tab_lock_scheme
       exec (@lock_datarows_str)
    end


create table #temp
(
 id int identity,
 column_name varchar(200),
 default_name varchar(7500)
)
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
order by d.id,d.colid
select @rows = @@rowcount
select @pre_column_name=''
while @n <= @rows
begin
select  @column_name=column_name,@default_name=default_name from #temp where id=@n
if @n=1
select @default_str ='alter table '+@objname+'_tmp replace '+@column_name+' '+@default_name
if @n>1 and  @column_name!= @pre_column_name
 begin
   exec (@default_str)
   if @@error  != 0
     begin
       update dba_delete_history_data
       set default_fail_sql=default_fail_sql+left(@default_str,400)+'     '
       where table_name=@objname
     end
   select @default_str ='alter table '+@objname+'_tmp replace '+@column_name+' '+@default_name
 end
if  @n>1 and  @column_name = @pre_column_name
    select  @default_str=@default_str+@default_name
    
select @pre_column_name=@column_name
select @n = @n + 1
end


if @rows >0
 exec (@default_str)
 if @@error  != 0
    begin
     update dba_delete_history_data
     set default_fail_sql=default_fail_sql+left(@default_str,400)+'     '
     where table_name=@objname
    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:
   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


create table #temp_protect
  (
    id int identity,
    table_name varchar(200),
    user_name varchar(200),
    action  int,
    protecttype int
  )
declare @protect_n        int
declare @protect_rows     int
    declare @protect_str varchar(200)
select @protect_n=1
insert #temp_protect(table_name,user_name,action,protecttype)
 select o.name,u.name,p.action,p.protecttype
from sysprotects p,sysobjects o,sysusers u where p.id=o.id and u.uid=p.uid and o.name=@objname


select @protect_rows = @@rowcount
while @protect_n <= @protect_rows
begin
select  @protect_str=(case when protecttype=0 then 'grant with grant '
when protecttype=1 then 'grant '
when protecttype=2 then  'revoke ' end) +
(case when action=151 then 'references'
when action=167 then 'set proxy'
when action=187 then 'set statistics on'
when action=188 then 'set statistics off'
when action=193 then 'select'
when action=195 then 'insert'
when action=196 then 'delete'
when action=197 then 'update'
when action=198 then 'create table'
when action=203 then 'create database'
when action=205 then 'grant'
when action=206 then 'revoke'
when action=207 then 'create view'
when action=221 then 'create trigger'
when action=222 then 'create procedure'
when action=224 then 'execute'
when action=228 then 'dump database'
when action=233 then 'create default'
when action=235 then 'dump transaction'
when action=236 then 'create rule'
when action=253 then 'connect'
when action=282 then 'delete statistics'
when action=317 then 'dbcc'
when action=320 then 'truncate table'
when action=326 then 'update statistics'
when action=347 then 'set tracing' end) +
' on '+
table_name+
'_tmp to '+
user_name  
from #temp_protect where id = @protect_n
exec (@protect_str)
select @protect_n = @protect_n + 1
end
drop table #temp_protect


if(lower(@is_bak)='y')
begin
 set @drop_temp="if exists(select 1 from sysobjects where type='U' and name='"+@objname+'_'+@data_str+"') begin truncate table "+@objname+'_'+@data_str+" drop table "+@objname+'_'+@data_str+"  end"
      exec (@drop_temp)
 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 and default_fail_sql =''
set nocount off
end
go
sp_procxmode up_delete_history_table, anymode
go

if exists (select 1
            from  sysobjects
            where id = object_id('up_delete_history_data')
            and   type = 'P')
   drop procedure up_delete_history_data
go


create procedure up_delete_history_data
@objnames varchar(8000),
@history_time varchar(20),
@is_bak varchar(1)
as
declare @condition_name varchar(1000)
declare @objname varchar(50)
declare @lock_scheme varchar(50)
select @objnames=','+@objnames+','
begin
    if(lower(@is_bak) not in ('n','y'))
   begin
     print "the third parameter means is bakup all source tables, must be 'n' or 'y'"
return -1
   end
   if @objnames is null or char_length(@objnames)=0
        begin
             print "please input source tables"
            return -1
        end
   if @history_time is null or @history_time>getdate()
      begin
         print "please input history_time and histroy_time must less than current date"
         return -1
      end
select @lock_scheme=value2 from master..sysconfigures where config=327 and name='lock scheme'


   if charindex(',ew_sending_queue,',@objnames) > 0
    begin
 set  @objname='ew_sending_queue'
 set  @condition_name='where send_time > convert(char(10),dateadd (dd ,-5,getdate()),111)'
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
if charindex(',sms_sending,',@objnames) > 0
    begin
 set  @objname='sms_sending'
 set  @condition_name='(index idx_send_time) where send_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',sms_receiving,',@objnames) > 0
    begin
 set  @objname='sms_receiving'
 set  @condition_name='where receive_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',sms_timing,',@objnames) > 0
    begin
 set  @objname='sms_timing'
 set  @condition_name='(index idx_creation_time) where creation_time > convert(char(10),dateadd (dd ,-5,getdate()),111)'
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',si_record,',@objnames) > 0
    begin
 set  @objname='si_record'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',msg_comment,',@objnames) > 0
    begin
 set  @objname='msg_comment'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',msg_homework,',@objnames) > 0
    begin
 set  @objname='msg_homework'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',msg_notice,',@objnames) > 0
    begin
 set  @objname='msg_notice'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',msg_sending,',@objnames) > 0
    begin
 set  @objname='msg_sending'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',msg_receiving,',@objnames) > 0
    begin
 set  @objname='msg_receiving'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',ex_fjabt_student_record,',@objnames) > 0
    begin
 set  @objname='ex_fjabt_student_record'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',ex_fjabt_teacher_record,',@objnames) > 0
    begin
 set  @objname='ex_fjabt_teacher_record'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',adc_interface_log,',@objnames) > 0
    begin
 set  @objname='adc_interface_log'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',adc_userbind_log,',@objnames) > 0
    begin
 set  @objname='adc_userbind_log'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',fee_pay_detail,',@objnames) > 0
    begin
 set  @objname='fee_pay_detail'
 set  @condition_name='where pay_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',log_card_login,',@objnames) > 0
    begin
 set  @objname='log_card_login'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',sys_operation_log,',@objnames) > 0
    begin
 set  @objname='sys_operation_log'
 set  @condition_name='where operated_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_student_signin,',@objnames) > 0
    begin
 set  @objname='st_student_signin'
 set  @condition_name='where statistic_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_teacher_signin,',@objnames) > 0
    begin
 set  @objname='st_teacher_signin'
 set  @condition_name='where statistic_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_device,',@objnames) > 0
    begin
 set  @objname='st_device'
 set  @condition_name='where stat_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_fjabt_student_record,',@objnames) > 0
    begin
 set  @objname='st_fjabt_student_record'
 set  @condition_name='(index pk_st_fjabt_student_record) where statistic_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_fjabt_teacher_record,',@objnames) > 0
    begin
 set  @objname='st_fjabt_teacher_record'
 set  @condition_name='(index pk_st_fjabt_teacher_record) where statistic_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_school,',@objnames) > 0
    begin
 set  @objname='st_school'
 set  @condition_name='where statistic_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_user,',@objnames) > 0
    begin
 set  @objname='st_user'
 set  @condition_name='where statistic_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',log_module_access,',@objnames) > 0
    begin
 set  @objname='log_module_access'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',log_user_operation,',@objnames) > 0
    begin
 set  @objname='log_user_operation'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',eb_login_log,',@objnames) > 0
    begin
 set  @objname='eb_login_log'
 set  @condition_name='where login_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',fp_call_bill,',@objnames) > 0
    begin
 set  @objname='fp_call_bill'
 set  @condition_name='where creation_time > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_card_class,',@objnames) > 0
    begin
 set  @objname='st_card_class'
 set  @condition_name='(index pk_st_card_class) where stat_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_student_class,',@objnames) > 0
    begin
 set  @objname='st_student_class'
 set  @condition_name='(index pk_st_student_class) where stat_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_sms_analysis_class,',@objnames) > 0
    begin
 set  @objname='st_sms_analysis_class'
 set  @condition_name='(index pk_st_sms_analysis_class) where stat_date > '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
   if charindex(',st_student_record,',@objnames) > 0
    begin
 set  @objname='st_student_record'
 set  @condition_name='(index idx_school_id) where month >= convert(int,datepart(year,+'''+@history_time+'''))*100+convert(int,datepart(month,+'''+@history_time+'''))'
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
 if charindex(',ew_score_detail,',@objnames) > 0
    begin
 set  @objname='ew_score_detail'
 set  @condition_name='where process_time> '''+@history_time+''''
 exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme
end
end
go
sp_procxmode up_delete_history_data, anymode
go