"Go" SQL Server to export indexes

Transferred from: http://blog.itpub.net/16436858/viewspace-609341/

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_descending_key as Is_descending_key
, C.is_primary_key
, C.is_unique_constraint
From Sys.tables as a
INNER JOIN 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
INNER JOIN Sys.index_columns as D on d.object_id = c.object_id and d.index_id = c.index_id
INNER JOIN sys.columns as E on e.object_id = d.object_id and e.column_id = d.column_id
INNER JOIN sys.data_spaces as f on f.data_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.schema_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_index_column_name + ') '
Else ' CREATE ' + case if 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 and then ' INCLUDE (' + includeindex.ix_included_column_name + ') ' ELSE ' END
+ ' on [' + A.fg_name + '] ' END
, case if 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_included_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 PATH (")
), 1, 1, ")
) Indexcolumns
OUTER APPLY
(
SELECT Ix_included_column_name
= STUFF ((
SELECT ', ' + column_name
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=1
ORDER by index_column_id
For XML PATH (")
), 1, 1, ")
) Includeindex
ORDER by A.schema_name,a.table_name,a.ix_name;
END

"Go" SQL Server to export indexes