1: -- ===========================================================

   2: -- Unique Indexes 

   3: -- How to script out Unique Indexes in SQL Server 2005

   4: -- Set Results to Text so you can copy and paste the result

   5: -- ===========================================================

   6: SET NOCOUNT ON

   7:

   8: -- Drop Unique Indexes

   9: SELECT

  10:     DISTINCT

  11:     ' DROP INDEX ' +

  12:     QuoteName(i.name) +

  13:     ' ON ' +

  14:     QuoteName(OBJECT_NAME(i.object_id))

  15: FROM

  16:     sys.index_columns cc

  17:     INNER JOIN sys.indexes i ON cc.object_id = i.object_id

  18:     AND cc.index_id = i.index_id

  19:     INNER JOIN sys.objects so

  20:     ON i.object_id = so.object_id

  21: WHERE

  22:     is_primary_key = 0 AND

  23:     is_unique = 1 AND

  24:     so.type = 'U'

  25:

  26: -- Recreate Unique Indexes

   27: SELECT

  28:     DISTINCT

  29:     'CREATE UNIQUE ' +

  30:     CASE OBJECTPROPERTY(so.object_id, N'CnstIsClustKey')

  31:     WHEN 1 THEN 'CLUSTERED '

  32:     ELSE ''

  33:     END +

  34:     ' INDEX ' +

  35:     QuoteName(i.name) +

  36:     ' ON ' +

  37:     QuoteName(OBJECT_NAME(i.object_id))+

  38:     '('+ LEFT(UniqueCols.col, LEN(UniqueCols.col) -1)

  39:     +')' +

  40:     CASE ISNULL(LEN(IncludedCols.col), 0)

  41:         WHEN 0 THEN ''

  42:         ELSE ' INCLUDE (' + LEFT(IncludedCols.col, LEN(IncludedCols.col) -1) + ')'

  43:     END

  44: FROM

  45:     sys.index_columns cc

  46:     INNER JOIN sys.indexes i ON cc.object_id = i.object_id

  47:     AND cc.index_id = i.index_id

  48:     INNER JOIN sys.objects so

  49:     ON i.object_id = so.object_id

  50: CROSS APPLY

  51: (

  52:     SELECT

  53:         sc.name + ','

  54:     FROM

  55:         sys.index_columns idxcol

  56:         INNER JOIN sys.columns sc

  57:         ON idxcol.column_id=sc.column_id

  58:         AND idxcol.object_id=sc.object_id

  59:     WHERE

  60:         idxcol.object_id = i.object_id

  61:         AND i.index_id = idxcol.index_id

  62:         AND is_included_column = 0

  63:     FOR XML PATH('')

  64: )UniqueCols(col)

  65: CROSS APPLY

  66: (

  67:     SELECT

  68:         sc.name + ','

  69:     FROM

  70:         sys.index_columns idxcol

  71:         INNER JOIN sys.columns sc

  72:         ON idxcol.column_id=sc.column_id

  73:         AND idxcol.object_id=sc.object_id

  74:     WHERE

  75:         idxcol.object_id = i.object_id

  76:         AND i.index_id = idxcol.index_id

  77:         AND is_included_column = 1

  78:     FOR XML PATH('')

  79: )IncludedCols(col)

  80: WHERE

  81:     is_primary_key = 0 AND

  82:     is_unique = 1 AND

  83:     so.type = 'U'

  84:

  85: SET NOCOUNT OFF