sybase 自动清理数据表的过程

迷南。 2022-06-14 09:26 251阅读 0赞

之前发过一个询问贴：  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