BEGIN with TX as (SELECT a.object_id, b.name as Schema_name , A.name as TABLE_NAME, c.name as Ix_name, C.is_unique as Ix_unique , C.type_desc as Ix_type_desc, d.index_column_id, d.is_included _column, e.name as column_name, f.name as Fg_name, d.is_des Cending_key as Is_descending_key, C.is_primary_key, C.is_unique_constraint From Sys.tables as a INNER joins Sys.schemas as B on a.schema_id = b.schema_id and a . is_ms_shipped = 0 INNER JOIN sys.indexes as C on a.object_id = c.object_id INN ER JOIN Sys.index_columns as D on d.object_id = c.object_id and d.index_id = c.index_id INNER JOIN sy S.columns as E On e.object_id = d.object_id and e.column_id = d.column_id INNER joins sys.data_spaces as F on F.D ata_space_id = c.data_space_id) SELECT Drop_index = case when (A.is_primary_key = 1 OR a.is _unique_constraint = 1) Then ' ALTER TABLE ' + a.table_name + ' DROP constraint ' + a.ix_ Name ELSE ' DROP INDEX ' + a.ix_name collate sql_latin1_general_cp1_ci_as + ' on ' + A.sc Hema_name + '. ' + a.table_name END, Create_index = case when (A.is_primary_key = 1 OR a.is_unique_constraint = 1) Then ' ALTER TABLE ' + a.table_name + ' ADD CONSTRAINT ' + a.ix_name + Case If A.is_primary_key = 1 Then ' primary key ' ELSE ' UNIQUE ' END + ' (' + indexcolumns.ix_i Ndex_column_name + ') ' Else ' CREATE ' + case-a.ix_unique = 1 Then ' unique ' else ' END + A.ix_type_desc + ' INDEX ' + a.ix_name collate sql_latin1_general_cp1_ci_as + ' on ' + a.schema_name + '. ' + A.table_name + ' (' + indexcolumns.ix_index_column_name + ') ' + Case when includeindex.ix_included_column_name are not NULL then ' INCLUDE (' + includeindex.ix_i Ncluded_column_name + ') ' ELSE ' end + ' on [' + A.fg_name + '] ' end, C ASE when a.ix_unique = 1 Then ' unique ' END as Ix_unique, A.ix_type_desc, A.ix_name , A.schema_name, A.table_name, Indexcolumns.ix_index_column_name, includeindex.ix_in Cluded_column_name, A.fg_name, A.is_primary_key, A.is_unique_constraint From (SELECT DISTINCT ix_unique , Ix_type_desc , Ix_name, SCHEMA_NAME, TABLE_NAME, FG _name, Is_primary_key, Is_unique_constraint from TX) as A OUTER APPLY (SELECT ix_index_column_name = STUFF ( SELECT ', ' + column_name + case If Is_descending_key = 1 Then ' DESC ' ELSE ' END From TX as b WHERE schema_name = A.schema_name and Table_name=a.table_name and Ix_name=a.ix_name and Ix_type_desc=a.ix_type_desc and Fg_name=a.fg_name and Is_included_column=0 ORDER by index_column_id for XML P ATH (") ) Indexcolumns OUTER APPLY (SELECT ix_included_column_name = STUFF ((SELECT ', ' + column_name FRO M TX as b WHERE schema_name = A.schema_name and Table_na Me=a.table_name and Ix_name=a.ix_name and Ix_type_desc =a.ix_type_desc and Fg_name=a.fg_name and Is_included_ Column=1 ORDER by index_column_id for XML PATH (") ) Includeindex order by A.schema_name,a.table_name,a.ix_name; END