|
CREATE?proc?[dbo].[reBuildIndex]
as
declare?@statement?NVARCHAR(1000)
declare?mycursor?cursor?for
SELECT?'ALTER?INDEX?['?+?ix.name?+?']?ON?['?+?s.name?+?'].['?+?t.name?+?']?REBUILD?WITH?(?PAD_INDEX??=?OFF,?STATISTICS_NORECOMPUTE??=?OFF,?ALLOW_ROW_LOCKS??=?ON,?ALLOW_PAGE_LOCKS??=?ON,?SORT_IN_TEMPDB?=?OFF,?ONLINE?=?OFF?)'?
??????as?sqlStr
FROM???sys.indexes?AS?ix
???????INNER?JOIN?sys.tables?t
???????ON?????t.object_id?=?ix.object_id
???????INNER?JOIN?sys.schemas?s
???????ON?????t.schema_id?=?s.schema_id
???????INNER?JOIN
??????????????(SELECT?object_id???????????????????,
??????????????????????index_id????????????????????,
??????????????????????avg_fragmentation_in_percent,
??????????????????????partition_number
??????????????FROM????sys.dm_db_index_physical_stats?(DB_ID(),?NULL,?NULL)
??????????????)?ps
???????ON?????t.object_id?=?ps.object_id
??????????AND?ix.index_id?=?ps.index_id
???????INNER?JOIN
??????????????(SELECT??object_id,
???????????????????????index_id?,
???????????????????????COUNT(DISTINCT?partition_number)?AS?partition_count
??????????????FROM?????sys.partitions
??????????????GROUP?BY?object_id,
???????????????????????index_id
??????????????)?pc
???????ON?????t.object_id??????????????=?pc.object_id
??????????AND?ix.index_id??????????????=?pc.index_id
WHERE??ps.avg_fragmentation_in_percent?>?0
???AND?ix.name?IS?NOT?NULL
open?mycursor
fetch?next?from?mycursor??into?@statement while(@@fetch_status=0)?????--如果数据集里一直有数据 begin EXEC?sp_executesql??@statement --select?@statement fetch?next?from?mycursor??into?@statement end close?mycursor??????? deallocate?mycursor ?
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
