/* --------------------------------------------------
통계 정보 업데이트
-------------------------------------------------- */
SELECT 'USE [' + NAME + ']; EXEC SP_UPDATESTATS;'
FROM SYS.DATABASES
WHERE DATABASE_ID > 4
GO
/* --------------------------------------------------
인덱스 조각화 상태 조회 및 리빌드
-------------------------------------------------- */
SELECT [ALTER] = 'ALTER INDEX'
, INDEX_NAME = B.NAME
, [ON] = 'ON'
, TABLE_SCHEMA = C.TABLE_SCHEMA + '.' + OBJECT_NAME(A.OBJECT_ID)
, [REBUILD] = 'REBUILD'
, [OPTION] = 'WITH ( SORT_IN_TEMPDB = ON );'
, FRAG = A.AVG_FRAGMENTATION_IN_PERCENT
, PAGES = A.PAGE_COUNT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL, NULL, NULL, NULL) AS A
JOIN SYS.INDEXES AS B ON (A.OBJECT_ID = B.OBJECT_ID) AND B.INDEX_ID = A.INDEX_ID
JOIN INFORMATION_SCHEMA.TABLES AS C ON (C.TABLE_NAME = OBJECT_NAME(A.OBJECT_ID)) AND A.DATABASE_ID = DB_ID() AND OBJECTPROPERTY(B.OBJECT_ID, 'ISSYSTEMTABLE')=0
WHERE A.INDEX_ID > 0
AND A.PAGE_COUNT > 100
AND A.AVG_FRAGMENTATION_IN_PERCENT > 10.00