Merhaba,
Konu başlığından da anlaşılabileceği gibi çalıştığım firma içerisinde bir veritabanındaki indexleri taşınması gibi bir süreç vardı. Bununla ilgili aşağıdaki kodu kullanabilirsiniz..
WITH indexCTE AS
(
SELECT DISTINCT
i.index_id, i.name, i.object_id
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE
EXISTS (SELECT * FROM sys.columns c
WHERE
–c.collation_name = ‘Modern_Spanish_CI_AS’ AND
c.column_id = ic.column_id AND c.object_id = ic.object_id
–and c.name =’TROrder’
)
),
–select * from indexCTE
indexCTE2 AS
(
SELECT
indexCTE.name ‘IndexName’,
OBJECT_NAME(indexCTE.object_id) ‘TableName’,
CASE indexCTE.index_id
WHEN 1 THEN ‘CLUSTERED’
ELSE ‘NONCLUSTERED’
END AS ‘IndexType’,
(SELECT CASE WHEN ic.is_descending_key = 1 THEN c.name + ‘ DESC ,’
ELSE c.name + ‘ ASC ,’
END
FROM
sys.columns c
INNER JOIN
sys.index_columns ic
ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.is_included_column = 0
WHERE
indexCTE.object_id = ic.object_id
AND indexCTE.index_id = ic.index_id
FOR XML PATH(”)
) ixcols,
ISNULL(
(SELECT DISTINCT c.name + ‘,’
FROM
sys.columns c
INNER JOIN
sys.index_columns ic
ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.is_included_column = 1
WHERE
indexCTE.object_id = ic.object_id
AND indexCTE.index_id = ic.index_id
FOR XML PATH(”)
), ”) includedcols
FROM
indexCTE
)
SELECT
‘CREATE ‘ + IndexType + ‘ INDEX ‘ + IndexName + ‘ ON ‘ + TableName +
‘(‘ + SUBSTRING(ixcols, 1, LEN(ixcols)-1) +
CASE LEN(includedcols)
WHEN 0 THEN ‘)’
ELSE ‘) INCLUDE (‘ + SUBSTRING(includedcols, 1, LEN(includedcols)-1) + ‘)’
END
FROM
indexCTE2
ORDER BY
TableName–, IndexName
Ekteki dosyayıda kullanabilirsiniz..