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;
Related Reference links :
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 :
No comments:
Post a Comment