Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You're pretty close, I'd say - I tried this, can you verify if this works for you and shows you the expected 122 indices to be recreated??</p> <p><strong>UPDATE</strong>: added functionality to determine CLUSTERED vs. NONCLUSTERED index type, and to add INCLUDEd columns to the index definition.</p> <pre><code>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) ), 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 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 = 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 </code></pre> <p>Do you get the <code>CREATE INDEX</code> statements you're looking for??</p> <p>Marc</p>
    singulars
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. This table or related slice is empty.
    2. VO
      singulars
      1. This table or related slice is empty.
    3. VO
      singulars
      1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload