转自：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 WHEN a.is_primary_key = 1 THEN ' PRIMARY KEY' ELSE ' UNIQUE' END + '(' + indexColumns.ix_index_column_name + ')'

ELSE 'CREATE ' + CASE WHEN 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 IS NOT NULL THEN ' INCLUDE (' + IncludeIndex.ix_included_column_name + ')' ELSE '' END

+ ' ON [' + a.fg_name +']' END

,CASE 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_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 WHEN 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

---