All about SQLServer: TSQL Script to Generate Create ColumnStore Index Script

This article is about the TSQL script which can be used to generate create ColumnStore Index script from the database .

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT ' CREATE ' + 
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +   
    QUOTENAME(I.name)  + ' ON '  +  
    QUOTENAME(Schema_name(T.Schema_id))+'.'+QUOTENAME(T.name) + ' ( ' + 
    KeyColumns + ' )  WITH (' + 
    -- default value  
    ' DROP_EXISTING = OFF ' + 
    ' ) ON [' + 
   DS.name + ' ] '  [CreateIndexScript] 
FROM sys.indexes I   
 JOIN sys.tables T ON T.Object_id = I.Object_id    
 JOIN (SELECT * FROM (  
    SELECT IC2.object_id , IC2.index_id ,  
        STUFF((SELECT ' , ' + C.name
    FROM sys.index_columns IC1  
    JOIN Sys.columns C   
       ON C.object_id = IC1.object_id   
       AND C.column_id = IC1.column_id   
       AND (IC1.is_included_column = 1 
       AND IC1.index_id = 6 )
    WHERE IC1.object_id = IC2.object_id   
       AND IC1.index_id = IC2.index_id   
    GROUP BY IC1.object_id,C.name,index_id  
    ORDER BY MAX(IC1.key_ordinal)  
       FOR XML PATH('')), 1, 2, '') KeyColumns   
    FROM sys.index_columns IC2
    WHERE (IC2.is_included_column = 1 
       AND IC2.index_id = 6 )  
    --AND IC2.Object_id = object_id('Person.Person') --Comment for all tables 
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4   
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id  
WHERE I.index_id = 6

Related Reference links :