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