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.rowside
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 #indextmp
FROM 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_Index
FETCH NEXT FROM Cur_Index INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
SET @DBCCString = 'DBCC DBREINDEX(@TblName,'''')WITH NO_INFOMSGS'it
EXEC SP_EXECUTESQL @DBCCString,N'@TblName VARCHAR(100)',@TableName
PRINT '重建表' + @TableName +'的索引........OK!'
FETCH NEXT FROM Cur_Index INTO @TableName
END
CLOSE Cur_Index
DEALLOCATE Cur_Indexio
PRINT '操做完成！'class
--select count(*) from TMPC494F5680CD811E9A168005056Btest
--exec sp_updatestats --重建计数器object