"Go" SQL Server get Index script

There are a lot of statements about how to get an index script, and the last time I had to query and get the index script in the project, I wrote a simple query statement to get it.

With Idxcol
As (SELECT
I.OBJECT_ID,
I.INDEX_ID,
object_name (i.object_id) as objname,
I.name as Idxname,
Ocol.name as ColName,
I.type as Idxtype,
I.type_desc as Idxtypedesc,
I.is_unique,
I.is_primary_key,
I.is_unique_constraint,
I.fill_factor,
Icol.key_ordinal as Idxcoloder,
Icol.is_descending_key,
Icol.is_included_column,
Pt.row_count,
Pt.used_page_count * 8 *1024.0/power (1024x768, 2) as [USEDROWPAGE_MB],
Pt.reserved_page_count * 8 *1024.0/power (1024x768, 2) as [ALLROWPAGE_MB]--,
--*
From
Sys.indexes I,
Sys.index_columns Icol,
Sys.columns Ocol,
Sys.dm_db_partition_stats PT
WHERE
i.object_id = icol.object_id
and i.index_id = icol.index_id
and icol.object_id = ocol.object_id
and icol.column_id = ocol.column_id
and i.object_id = pt.object_id
and i.index_id = pt.index_id
and EXISTS (SELECT
1
From
Sys.objects o
WHERE
o.object_id = i.object_id

and O.type = ' U '))

SELECT
* ,
N ' CREATE ' + t.idxtypedesc COLLATE Latin1_general_ci_as_ks_ws +
N ' INDEX ' + t.idxname COLLATE Latin1_general_ci_as_ks_ws +
N ' on ' + t.objname COLLATE Latin1_general_ci_as_ks_ws +
N ' (' + case when t.colsinc are NULL then
T.cols COLLATE Latin1_general_ci_as_ks_ws
ELSE
--replace (Cols,t.colsinc, ') COLLATE Latin1_general_ci_as_ks_ws
SUBSTRING (Cols,len (colsinc) +2,len (cols)-len (colsinc))
END
+ N ') ' +case when t.colsinc are not NULL and then ' INCLUDE (' +t.colsinc+ ') ' ELSE ' END
From
(SELECT
DISTINCT
OBJECT_ID,
INDEX_ID,
ObjName,
Idxname,
Idxtypedesc,
case if Is_primary_key = 1 Then ' prmiary key '
ELSE case If Is_unique_constraint = 1 Then ' unique constraint '
ELSE case If Is_unique = 1 Then ' unique '
ELSE '
END + Idxtypedesc
END
END as Typedesc,
STUFF ((SELECT
', ' + colname + case When Is_descending_key = 1 Then ' desc '
ELSE '
END
From
Idxcol
WHERE
object_id = c.object_id
and index_id = c.index_id
ORDER by
Idxcoloder
For
XML PATH (")), 1, 1, ') as cols,
STUFF ((SELECT
', ' + colname
From
Idxcol
WHERE
object_id = c.object_id
and index_id = c.index_id
and Is_included_column = 1
ORDER by
Idxcoloder
For
XML PATH (")), 1, 1, ') as Colsinc,
Row_count,
[ALLROWPAGE_MB],
[USEDROWPAGE_MB],
[ALLROWPAGE_MB]-[USEDROWPAGE_MB] as UNUSEDROWPAGE_MB
From
Idxcol c) as T

"Go" SQL Server get Index script