Script existing indices (SQL Server 2k8)

I use this script to script indices for the selected table:

Code:

-- script existing indices on a table
$PROMPT(script_disabled,Script disabled indices?,,,Yes No)$
$PROMPT(drope,Drop existing indices?,,,1 0,Yes No)$
$PROMPT(usei,Include INCLUDE parts?,,,1 0,Yes No)$

$$DECLARE @targy VARCHAR(256), @elso_pont INT, @masodik_pont INT, @len INT, @sema VARCHAR(256), @tabla VARCHAR(256), @mezo VARCHAR(256)
SELECT @targy = '$OBJECT$', @elso_pont = CHARINDEX('.',@targy), @masodik_pont = CHARINDEX('.',@targy,CHARINDEX('.',@targy)+1), @len = LEN(@targy)
IF @masodik_pont = 0 SELECT @sema = PARSENAME(@targy,2), @tabla = PARSENAME(@targy,1) ELSE SELECT @sema = PARSENAME(@targy,3), @tabla = PARSENAME(@targy,2), @mezo = PARSENAME(@targy,1)

DECLARE @sql NVARCHAR( MAX )
SET @sql = ''
SELECT
@sql = @sql +
+ CASE
   WHEN ic.index_column_id = 1 AND i.is_primary_key = 1 AND $drope$ = 1
      AND RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN ''
      + CASE
         WHEN i.is_disabled = 1 THEN '-- WARNING !!! INDEX DISABLED !!! WARNING --' -- + CHAR( 10 )
         ELSE '-- LIVE INDEX! --'
      END
      + CHAR( 10 )
      + 'IF EXISTS (SELECT * FROM sys.indexes WHERE '
      + 'object_id = OBJECT_ID(N''' + s.name + '.' + t.name + ''') AND name = N''' + i.name + ''')' + CHAR( 10 )
      + 'ALTER TABLE ' + s.name + '.' + t.name +
      ' DROP CONSTRAINT ' + i.name + CHAR( 10 ) + 'GO' + CHAR( 10 )
      + CHAR( 10 )
   WHEN ic.index_column_id = 1 AND i.is_primary_key = 0 AND $drope$ = 1
      AND RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN ''
      + CASE
         WHEN i.is_disabled = 1 THEN '-- WARNING !!! INDEX DISABLED !!! WARNING --' + CHAR( 10 )
         ELSE '-- LIVE INDEX! --'
      END
      + CHAR( 10 )
      + 'IF EXISTS (SELECT * FROM sys.indexes WHERE '
      + 'object_id = OBJECT_ID(N''' + s.name + '.' + t.name + ''') AND name = N''' + i.name + ''')' + CHAR( 10 )
      + 'DROP INDEX ' + i.name + ' ON ' + s.name + '.' + t.name
      + CHAR( 10 ) + 'GO' + + CHAR( 10 ) + CHAR( 10 )
   ELSE ''
   END
+ CASE
   WHEN ic.index_column_id = 1 AND i.is_primary_key = 1
      AND RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN 'ALTER TABLE '
      + s.name + '.' + t.name + ' ADD CONSTRAINT ' + i.name + ' PRIMARY KEY ' + CASE i.[type]
         WHEN 1 THEN 'CLUSTERED'
         WHEN 2 THEN 'NONCLUSTERED'
         ELSE '!!!error 2!!!'
      END
   WHEN ic.index_column_id = 1 AND i.is_primary_key = 0
      AND RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN 'CREATE '
      + CASE i.is_unique
         WHEN 0 THEN ''
         WHEN 1 THEN 'UNIQUE '
         ELSE '!!!error UNIQUE!!!'
      END
      + CASE i.type
         WHEN 1 THEN 'CLUSTERED'
         WHEN 2 THEN 'NONCLUSTERED'
         ELSE '!!!error CLUSTER!!!'
      END
      + ' INDEX ' + i.name + ' ON ' + t.name
   ELSE ''
   END
+ CASE
   WHEN RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN CHAR( 10 ) + '('
   WHEN ic.is_included_column = 1 AND $usei$ = 1
      AND RANK() OVER( PARTITION BY i.index_id ,ic.is_included_column ORDER BY ic.index_column_id ) = 1 THEN CHAR( 10 )
      + ')' + CHAR( 10 ) + 'INCLUDE' + CHAR( 10 ) + '('
   ELSE ''
   END
+ CASE
   WHEN ( RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 )
      OR ( RANK() OVER( PARTITION BY i.index_id ,ic.is_included_column ORDER BY ic.index_column_id ASC ) = 1 ) THEN ''
      + CASE
         WHEN ic.is_included_column = 0 OR $usei$ = 1 THEN CHAR( 10 ) + ' ' + c.name
         ELSE ''
      END
   ELSE CASE
         WHEN ic.is_included_column = 0 OR $usei$ = 1 THEN CHAR( 10 ) + ' ,' + c.name
         ELSE ''
      END
   END
+ CASE
   WHEN ic.is_descending_key = 1 AND ic.is_included_column = 0 THEN ' DESC'
   WHEN ic.is_descending_key = 0 AND ic.is_included_column = 0 THEN ' ASC'
   ELSE ''
   END
+ CASE
   WHEN RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id DESC ) = 1 THEN CHAR( 10 ) + ')' + CHAR( 10 ) + 'GO' + CHAR( 10 ) + CHAR( 10 )
      + CHAR( 10 )
   ELSE '' --ELSE CHAR( 10 ) + ')' + CHAR( 10 ) + 'GO' + CHAR( 10 ) --'!!!error 6!!!'
   END
FROM
sys.schemas s
INNER JOIN
sys.tables t
   ON t.schema_id = s.schema_id
INNER JOIN
sys.indexes i
   ON i.object_id = t.object_id
INNER JOIN
sys.index_columns ic
   ON ic.object_id = t.object_id
   AND ic.index_id = i.index_id
INNER JOIN
sys.columns c
   ON c.object_id = t.object_id
   AND ic.column_id = c.column_id
WHERE i.index_id > 0
AND i.type IN ( 1 ,2 ) -- clustered & nonclustered only
AND i.is_hypothetical = 0
AND ( i.is_disabled = 0 OR '$script_disabled$' = 'Yes')
AND s.name = @sema
AND t.name = @tabla
ORDER BY
i.index_id
,ic.index_column_id
SELECT @sql
$$

Thanks for sharing excellent information.Interesting it is a script submission.Sometimes as a DBA need to generate a T-SQL script for dropping and creating indexes in our SQL Server databases. T-SQL Script to Create All SQL Server Indexes

Here are some things to be aware of when generating the creation script:

If the index was disabled, then the code to disable it will also be generated.
If the index has included columns then they will be included in the same order.
The creation script considers all properties of the index (FILEGROUP, ALLOW_PAGE_LOCKS, STATISTICS_NORECOMPUTE, FILLFACTOR, SORT_IN_TEMPDB, etc.). You will not lose any index properties, the final script will create the original index.
Will not script indexes linked to the primary key
Will not script partitioned indexes

Thank you! This has really helped me to capture all the indexes.

This one I created when we were using SQL Server 2008. As you can see it's pretty aged now and it's far from being perfect. You're welcome to adapt it to your needs, refine or upgrade it in any way you see fit or necessary, and if done so, I'd be glad if you uploaded the upgraded version here so that we all can learn and benefit from it ;)