新增文章

文章标题分类内容

-- 执行语句：会占用服务器资源，影响性能，请业务空闲时操作

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 删除：临时表

select 'drop table '+ name +' ;'  from sysobjects   where xtype = 'U' and  ( name like'TMP%' )  order by name ;
------------------------------------------------------------------------------------------------------------------------------------
   declare @Sql nvarchar(max) = '' ;

select @Sql = @Sql +'drop table  '+ name +' ;'+ char(10)  from sysobjects   where xtype = 'U' and  ( name like'TMP%' )  order by name ;

print @Sql
     exec (@Sql) 
   go

select 'select * from '+ name +' ;'  from sysobjects   where xtype = 'U' and ( name like'T_%' ) and ( name not like'TMP%' )  order by name ;
-------------------------------------------------------------------------------------------------------------------------------------------------------------
   declare @Sql nvarchar(max) = '' ; 
   
    select @Sql = @Sql +'alter index all on  '+ name +'  rebuild ;'+ char(10)  from sysobjects   where xtype = 'U' and ( name like'T_%' ) and ( name not like'TMP%' )  order by name ;

print @Sql
     exec (@Sql) 
   go

/*
   alter index all on T_ECC_AFTERSALEORDERENTRY_C rebuild;
   alter index all on T_FA_COUNTINGRPTENTRY rebuild;
   alter index all on T_CB_BYPRODUCTCOST rebuild;
*/

declare @Sql nvarchar(max) = '' ;

select @Sql = @Sql +'update statistics  '+ name +' ;'+ char(10)  from sysobjects    where xtype = 'U' and ( name like'T_%' ) and ( name not like'TMP%' )  order by name ;

print @Sql
     exec (@Sql)
   go

/*
   update statistics  T_ECC_AFTERSALEORDERENTRY_C ;
   update statistics  T_FA_COUNTINGRPTENTRY ;
   update statistics  T_CB_BYPRODUCTCOST ;
*/

declare @Sql nvarchar(max) = '';

select @Sql = @Sql + 'alter table  '+ name +'  rebuild with (data_compression = row) ;'+ char(10)  from sysobjects   where xtype = 'U' and ( name like'T_%' ) and ( name not like'TMP%' )  order by name ;

print @Sql
      exec (@Sql) 
   go

/*
   alter table T_ECC_AFTERSALEORDERENTRY_C rebuild with (data_compression = row) ;
   alter table T_FA_COUNTINGRPTENTRY rebuild with (data_compression = row) ;
   alter table T_CB_BYPRODUCTCOST rebuild with (data_compression = row) ;
*/

dbcc shrinkDataBase(N'AIS20230320140850');
   go

/*
   AIS20230320140850_Data.mdf   11.3G - 6.52G
   AIS20230320140850_log.ldf     555M - 1M
*/