收縮數據庫后自動碎片整理 -开发者知识库

收縮數據庫后自動碎片整理 -开发者知识库,第1张

use xxDB
go

select * into #tempT1 from (SELECT name
FROM sys.dm_db_index_physical_stats (DB_ID(N'xxDB'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
and avg_fragmentation_in_percent>30 and name is not null) as t1

select * into #tempT2 from (SELECT
name
=IDX.Name,
tableName
=O.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN sys.objects O
ON O.[object_id]=IDX.[object_id]
INNER JOIN sys.columns C
ON O.[object_id]=C.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
AND IDXC.Column_id=C.Column_id
INNER JOIN -- 對於一個列包含多個索引的情況,只顯示第1個索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id) as t2

select * into #tempT3 from (select t1.name,tableName from #tempT1 t1 left join #tempT2 t2 on
t1.name
=t2.name group by t1.name,tableName) as t3

if exists(select 1 from #tempT3)
begin
DECLARE @var_index varchar(100),
@var_tableName varchar(100)

declare INDEX_CURSOR cursor local for
select name,tableName from #tempT3

open INDEX_CURSOR
fetch next from INDEX_CURSOR into @var_index,@var_tableName
while @@FETCH_STATUS=0
begin
exec('ALTER INDEX [' @var_index '] ON [dbo].[' @var_tableName '] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )')
fetch next from INDEX_CURSOR into @var_index,@var_tableName
end
close INDEX_CURSOR
deallocate INDEX_CURSOR
end
drop table #tempT1
drop table #tempT2
drop table #tempT3

 

最佳答案:

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复