title: Generate CREATE scripts for a list of indexes
body: <p>As part of a collation changing exercise, I have a list of indexes (122) that needs to be dropped and then re-created. How can I re-create these indexes without having to go through the GUI and scripting it to a query window each time?</p>
  
  <p>My list of indexes is obtained from this script</p>
  
  <pre><code>WITH indexCTE AS
      (   
      SELECT Table_Name, Column_Name, Collation_Name 
      FROM information_schema.columns 
      WHERE Collation_Name IS NOT NULL AND Collation_Name = 'Modern_Spanish_CI_AS'
      ), 
      indexCTE2 AS
      (
      SELECT i.Name [Index Name], OBJECT_NAME(i.object_ID) [Table Name], c.Name [Column Name]
      FROM sys.indexes i 
      INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
      INNER JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.OBJECT_ID
      WHERE EXISTS (SELECT 1 FROM indexCTE t1 WHERE t1.Table_Name = OBJECT_NAME(i.object_ID) AND t1.Column_Name = c.Name)
      ) SELECT * FROM indexCTE2
  </code></pre>
  
  <p>As you can probably tell, I'm still a Jr. DBA so please be patient with me!</p>
  
  <p>Thanks!</p>