# [SQL Server] 重建 INDEX 的語法
怕以後要用做個紀錄
環境是 sql server, 無法使用STRING_AGG的版本

WITH all_indexs AS (
	SELECT  
	    s.name AS SchemaName,
	    t.name AS TableName,
	    i.name AS IndexName,
	    ROW_NUMBER() OVER (PARTITION BY t.object_id ORDER BY i.name) AS IndexOrder,
	    i.type_desc AS IndexType,
	    i.is_unique,
	    i.is_primary_key,
	    STUFF(
	    	(SELECT ',' + c2.name + ' ' + CASE ic2.is_descending_key WHEN 0 THEN 'ASC' ELSE 'DESC' END
	        FROM sys.index_columns ic2
	        JOIN sys.columns c2 ON ic2.object_id = c2.object_id AND ic2.column_id = c2.column_id
	        WHERE ic2.object_id = i.object_id
	          AND ic2.index_id = i.index_id
	        ORDER BY ic2.key_ordinal
	        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
        ) AS IndexColumns
	FROM sys.schemas s
	JOIN sys.tables t ON s.schema_id = t.schema_id
	JOIN sys.indexes i ON t.object_id = i.object_id
	WHERE i.name IS NOT NULL AND is_primary_key=0
)
select 
	('DROP INDEX ' +  IndexName +' ON ' + TableName + ';') as drop_text,
	('CREATE INDEX ' + TableName + '_' + CAST(IndexOrder AS varchar) + ' ON ' + TableName + '(' +IndexColumns+ ');' ) as create_text
from all_indexs