Скрипт, для REBUILD, REORGANIZE всех индексов

Tsql теория > Скрипт, для REBUILD, REORGANIZE всех индексов
01.04.2013 14:08:24


Наиболее часто встречающиеся слова в статье:

[TSQL_Command] [object_id] [schema_name] [object_name] [avg_fragmentation_in_percent] [index_type_desc] [''ALTER] [index_name] [schema_id]


Статья:

--По мотивам http://technet.microsoft.com/en-us/library/ms189858.aspx

SELECT  * ,
        CASE T.TSQL_Command
          WHEN '' THEN ''
          ELSE CASE WHEN T.index_type_desc = 'HEAP'
                    THEN 'ALTER TABLE ' + QUOTENAME(T.[schema_name]) + '.' + QUOTENAME(T.[object_name]) + ' '
                         + T.TSQL_Command
                    WHEN T.index_type_desc IN ( 'XML', 'Spatial' )
                    THEN 'ALTER INDEX ' + QUOTENAME(T.index_name) + ' ON ' + QUOTENAME(T.[schema_name]) + '.'
                         + QUOTENAME(T.[object_name]) + ' ' + T.TSQL_Command
                    ELSE 'ALTER INDEX ' + QUOTENAME(T.index_name) + ' ON ' + QUOTENAME(T.[schema_name]) + '.'
                         + QUOTENAME(T.[object_name]) + ' ' + T.TSQL_Command + '  WITH (ONLINE = ON)'
               END
        END Stmt
FROM    (
          SELECT  s.[schema_id] ,
                  s.name [schema_name] ,
                  O.[object_id] ,
                  O.type_desc object_type_desc ,
                  O.name [object_name] ,
                  i.index_id ,
                  I.type_desc index_type_desc ,
                  i.name index_name ,
                  DDIPS.avg_fragmentation_in_percent ,
                  CASE WHEN DDIPS.avg_fragmentation_in_percent BETWEEN 6 AND 30 THEN 'REORGANIZE'
                       WHEN DDIPS.avg_fragmentation_in_percent > 30 THEN 'REBUILD'
                       ELSE ''
                  END [TSQL_Command]
          FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) DDIPS
          LEFT JOIN sys.objects O ON DDIPS.[object_id] = O.[object_id]
          LEFT JOIN sys.schemas S ON O.[schema_id] = S.[schema_id]
          LEFT JOIN sys.indexes I ON DDIPS.[object_id] = I.[object_id]
                                     AND DDIPS.index_id = I.index_id
        ) T
ORDER BY T.avg_fragmentation_in_percent DESC