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 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID( ''' +a.SCHEMA_NAME+'.'+ a.table_name+''') AND name ='''+a.ix_name+''')'
+ 'ALTER TABLE '+a.schema_name + '.'+ a.table_name+' DROP CONSTRAINT ' + a.ix_name
ELSE 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID( ''' +a.SCHEMA_NAME+'.'+ a.table_name+''') AND name ='''+a.ix_name+''')'
+ ' DROP INDEX ' + a.ix_name + ' 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.schema_name + '.'+ 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