清除临时表和重建索引（数据库优化）

--清除临时表

use AISXXXXX
go
declare @sql varchar(max)
set @sql='';

with t as
(select name from sys.tables where create_date<getdate() and (name like 'tm[^_]%' or name like 'GL[^_]%') )
delete from t1 from T_BAS_TEMPORARYTABLENAME t1
join t on t1.FTABLENAME=t.name;

with t1 as
(select name from sys.tables where create_date<getdate() and (name like 'tm[^_]%' or name like 'GL[^_]%'))
select @sql=@sql+'drop table '+name+char(13)
from t1;
exec(@sql)

--重建索引

use AISXXXXX
GO
declare @ftablename varchar(255),@findexname varchar(255)
declare auth_cur cursor for
SELECT distinct object_name(a.object_id) AS TB
FROM sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent>10 AND object_name(a.object_id)<>'' AND name<>'' AND object_name(a.object_id) NOT LIKE 'TMP%' and object_name(a.object_id) not like 'GL%'

open auth_cur
fetch next from auth_cur into @ftablename
while (@@fetch_status=0)
begin
EXEC('ALTER INDEX ALL ON [dbo].['+@ftablename+'] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
fetch next from auth_cur into @ftablename
end
close auth_cur
deallocate auth_cur
GO

----更新统计信息

use AISXXXXX
GO
declare @ftablename varchar(255),@findexname varchar(255)
declare auth_cur cursor for
SELECT distinct object_name(a.object_id) AS TB
FROM sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where object_name(a.object_id)<>'' AND name<>'' and name not like 'TMP%'

open auth_cur
fetch next from auth_cur into @ftablename
while (@@fetch_status=0)
begin
EXEC('update statistics ' +@ftablename +' WITH FULLSCAN')
fetch next from auth_cur into @ftablename
end
close auth_cur

 

----重建索引2

USE AISXXXXX
GO

/****** Object: StoredProcedure [dbo].[RebuildIndex_UpdateSTATISTICS] Script Date: 08/13/2020 17:07:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[RebuildIndex_UpdateSTATISTICS]
as begin
declare @SqlStr nvarchar(max)
set @SqlStr=''
select @SqlStr= @SqlStr+ + 'alter index all on '+name+ ' rebuild;' from sysobjects where xtype='U' and (name like 'T_%') and (name not like 'TMP%')
exec (@SqlStr)
declare @SqlStr1 nvarchar(max)
set @SqlStr1=''
select @SqlStr1= @SqlStr1+ + 'UPDATE STATISTICS '+name+ ';' from sysobjects where xtype='U' and (name like 'T_%') and (name not like 'TMP%')
exec (@SqlStr1)

declare @SqlStr2 nvarchar(max)
set @SqlStr2=''
select @SqlStr2= @SqlStr2+ + 'ALTER TABLE '+name+ ' rebuild WITH (DATA_COMPRESSION =ROW);' from sysobjects where xtype='U' and (name like 'T_%') and (name not like 'TMP%')
exec (@SqlStr2)
end

GO

 

exec RebuildIndex_UpdateSTATISTICS   //执行重建索引