DECLARE @DBCCString NVARCHAR(1000)

DECLARE @TableName VARCHAR(100)DECLARE Cur_Index CURSOR FOR--SELECT Name AS TblName FROM sysobjects WHERE xType='U' and Name like 'T_HS%' ORDER BY TblName-- SELECT-- TblName = obj.name-- FROM sys.objects obj-- JOIN sys.indexes idx on obj.object_id = idx.object_id-- JOIN sys.partitions prt on obj.object_id = prt.object_id-- JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id-- WHERE-- obj.type = 'U' AND idx.index_id IN (0, 1) --and obj.name like 'TBD%' -- GROUP BY obj.name, prt.rows having prt.rows>317 and prt.rows <1000--ORDER BY prt.rows

SELECT top 100 OBJECT_NAME(ind.OBJECT_ID) AS TblName

--, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, --indexstats.avg_fragmentation_in_percent ----into #indextmpFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent >80 and ind.name is not null and OBJECT_NAME(ind.OBJECT_ID) not like 'TMP%'ORDER BY indexstats.avg_fragmentation_in_percent DESC

FOR READ ONLY

OPEN Cur_IndexFETCH NEXT FROM Cur_Index INTO @TableNameWHILE @@FETCH_STATUS=0BEGINSET @DBCCString = 'DBCC DBREINDEX(@TblName,'''')WITH NO_INFOMSGS'

EXEC SP_EXECUTESQL @DBCCString,N'@TblName VARCHAR(100)',@TableName

PRINT '重建表' + @TableName +'的索引........OK!'FETCH NEXT FROM Cur_Index INTO @TableNameENDCLOSE Cur_IndexDEALLOCATE Cur_Index

PRINT '操作完成！'

--select count(*) from TMPC494F5680CD811E9A168005056B

--exec sp_updatestats --重建计数器