Rebuild Index

USE DBName
GO

DECLARE @DBName Nvarchar(30) = DB_NAME()

-- Temp Table
IF EXISTS ( SELECT name FROM tempdb..sysobjects WHERE name='##REBUILD')
BEGIN
DROP TABLE ##REBUILD
END

DECLARE @S1 Nvarchar(1000)

CREATE TABLE ##REBUILD( ReIndexType Nvarchar(10),
TableSchema Nvarchar(100),
TableName Nvarchar(100),
IndexName Nvarchar(100),
IndexType Nvarchar(100),
avg_fragmentation_in_percent Float,
avg_page_space_used_in_percent Float
)

-- Insert Need Rebuild Table
SET @S1 = 'INSERT ##REBUILD '
+'SELECT '+'''REBUILD'''+' AS ReIndexType, '
+'sch.name AS TableSchema, '
+'obj.name AS TableName, '
+'inx.name AS IndexName, '
+'index_type_desc AS IndexType, '
+'avg_fragmentation_in_percent AS avg_fragmentation_in_percent, '
+'avg_page_space_used_in_percent AS avg_page_space_used_in_percent '
+'FROM ['+@DBName+'].sys.dm_db_index_physical_stats (DB_ID('''+@DBName+'''), NULL,NULL, NULL, '+'''LIMITED'''+') AS phy '
+'INNER JOIN ['+@DBName+'].sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id '
+'INNER JOIN ['+@DBName+'].sys.objects AS obj ON phy.object_id = obj.object_id '
+'INNER JOIN ['+@DBName+'].sys.schemas AS sch ON obj.schema_id = sch.schema_id '
+'WHERE index_type_desc <> '+'''HEAP'''
+'AND fragment_count IS NOT NULL '
+'AND avg_fragment_size_in_pages IS NOT NULL '
+'AND page_count >1024 '
+'AND avg_fragmentation_in_percent > 30'

EXEC (@S1)

-- Insert Need ReOrganize Table
SET @S1 =  'INSERT ##REBUILD '
+'SELECT '+'''REORGANIZE'''+' AS ReIndexType, '
+'sch.name AS TableSchema, '
+'obj.name AS TableName, '
+'inx.name AS IndexName, '
+'index_type_desc AS IndexType, '
+'avg_fragmentation_in_percent AS avg_fragmentation_in_percent, '
+'avg_page_space_used_in_percent AS avg_page_space_used_in_percent '
+'FROM ['+@DBName+'].sys.dm_db_index_physical_stats (DB_ID('''+@DBName+'''), NULL,NULL, NULL, '+'''LIMITED'''+') AS phy '
+'INNER JOIN ['+@DBName+'].sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id '
+'INNER JOIN ['+@DBName+'].sys.objects AS obj ON phy.object_id = obj.object_id '
+'INNER JOIN ['+@DBName+'].sys.schemas AS sch ON obj.schema_id = sch.schema_id '
+'WHERE index_type_desc <> '+'''HEAP'''
+'AND fragment_count IS NOT NULL '
+'AND avg_fragment_size_in_pages IS NOT NULL '
+'AND page_count >1024 '
+'AND avg_fragmentation_in_percent BETWEEN 10 AND 30'

EXEC (@S1)

SELECT * FROM ##REBUILD
DROP TABLE ##REBUILD

GO

-- 參考
-- 我自己