· 6 years ago · Mar 15, 2019, 05:44 PM
1/*
2 Description: MOVE ALL INDEXES of specified @IndexType from @OldFileGroupName
3 to @NewFileGroupName
4 By: Max Vernon
5 Date: 2014-11-19
6 NOTE: Does not deal with partitioned indexes; only indexes that are
7 currently defined as located on @OldFileGroupName are moved.
8*/
9DECLARE @OldFileGroupName NVARCHAR(255);
10DECLARE @NewFileGroupName NVARCHAR(255);
11DECLARE @OnlineOption BIT;
12DECLARE @msg NVARCHAR(255);
13DECLARE @cmd NVARCHAR(MAX);
14/*
15@IndexType can be
16 0 = Heap
17 1 = Clustered
18 2 = Nonclustered
19 3 = XML
20 4 = Spatial
21*/
22DECLARE @IndexType INT;
23SET @IndexType = 0;
24SET @cmd = '';
25SET @msg = '';
26SET @OnlineOption = 0;
27SET @OldFileGroupName = 'PRIMARY';
28SET @NewFileGroupName = 'fgdata01';
29IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @OldFileGroupName)
30BEGIN
31 IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @NewFileGroupName)
32 BEGIN
33 IF @IndexType = 0
34 BEGIN
35 /*
36 Heaps require building a clustered index on the target filegroup,
37 then dropping the clustered index.
38 */
39 SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END +
40 'CREATE CLUSTERED INDEX [CX_' + o.name + '_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + '] ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' (' + QUOTENAME((SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id)) + ')
41WITH (FILLFACTOR=100, DATA_COMPRESSION=PAGE) ON ' + QUOTENAME(@NewFileGroupName) + ';
42DROP INDEX [CX_' + o.name + '_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + '] ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';
43'
44 FROM sys.indexes i
45 INNER JOIN sys.objects o on i.object_id = o.object_id
46 INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
47 INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
48 INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id
49 WHERE i.type = 0 /* HEAP */
50 AND o.type = 'U' /* USER_TABLE */
51 AND o.is_ms_shipped = 0
52 AND ds.name = @OldFileGroupName;
53 END
54 ELSE
55 BEGIN
56 SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END +
57
58 CASE WHEN i.is_primary_key = 1 AND i.type = 2
59 THEN '
60BEGIN TRANSACTION
61BEGIN TRY
62 ALTER TABLE ' + QUOTENAME(s.name) + '.'+ QUOTENAME(o.name) + '
63 DROP CONSTRAINT ' + QUOTENAME(i.name) + ';
64 ALTER TABLE ' + QUOTENAME(s.name) + '.'+ QUOTENAME(o.name) + '
65 ADD CONSTRAINT ' + QUOTENAME(i.name) + ' PRIMARY KEY NONCLUSTERED ('
66 ELSE
67 'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc + ' INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) +
68 '('
69 END +
70 (
71 SELECT STUFF((
72 SELECT ', ' + QUOTENAME(col_c.name) + CASE WHEN col_ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
73 FROM sys.indexes col_i
74 INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
75 INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
76 WHERE col_ic.is_included_column = 0
77 AND col_i.object_id = i.object_id
78 AND col_i.index_id = i.index_id
79 ORDER BY col_ic.key_ordinal
80 FOR XML PATH('')
81 ),1,2,'')
82 )
83 + ')' + CHAR(13) + CHAR(10) +
84 CASE WHEN (
85 SELECT COUNT(1)
86 FROM sys.indexes col_i
87 INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
88 INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
89 WHERE col_ic.is_included_column = 1
90 AND col_i.object_id = i.object_id
91 AND col_i.index_id = i.index_id
92 ) > 0
93 THEN
94 ' INCLUDE (' +
95 (SELECT STUFF((
96 SELECT ', ' + QUOTENAME(col_c.name)
97 FROM sys.indexes col_i
98 INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
99 INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
100 WHERE col_ic.is_included_column = 1
101 AND col_i.object_id = i.object_id
102 AND col_i.index_id = i.index_id
103 ORDER BY col_ic.key_ordinal
104 FOR XML PATH('')
105 ),1,2,'')
106 ) + ')'
107 ELSE ''
108 END +
109 CASE WHEN i.has_filter = 1 THEN ' WHERE ' + i.filter_definition ELSE '' END +
110 ' WITH (' +
111 CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN 'DROP_EXISTING = ON, ' ELSE '' END +
112 CASE WHEN i.fill_factor > 0 AND i.fill_factor < 100 THEN 'FILLFACTOR = ' + CONVERT(NVARCHAR(3), i.fill_factor) + ', ' ELSE '' END +
113 'PAD_INDEX = ' + CASE WHEN i.is_padded = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
114 'IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
115 CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN 'ONLINE = ' + CASE WHEN @OnlineOption = 1 THEN 'ON' ELSE 'OFF' END + ', ' ELSE '' END +
116 'ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
117 'ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
118 'DATA_COMPRESSION = ' + p.data_compression_desc +
119 ') ON ' +
120 '[' + @NewFileGroupName + '];'
121
122 + CASE WHEN i.is_primary_key = 1 AND i.type = 2
123 THEN '
124 COMMIT TRANSACTION
125END TRY
126BEGIN CATCH
127 ROLLBACK TRANSACTION
128END CATCH'
129 ELSE
130 '
131'
132 END
133 FROM sys.indexes i
134 INNER JOIN sys.objects o ON i.object_id = o.object_id
135 INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
136 INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
137 INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id
138 WHERE o.type = 'U' /* USER_TABLE */
139 AND i.type = @IndexType
140 AND o.is_ms_shipped = 0
141 AND ds.name = @OldFileGroupName
142 --AND i.name = 'AcisC5ModuleLaunc3'
143 ORDER BY o.name, i.name;
144 END
145 IF @cmd = ''
146 BEGIN
147 SET @msg = 'Warning: no indexes found on ' + @OldFileGroupName + '!';
148 RAISERROR (@msg, 0, 1) WITH NOWAIT;
149 END
150 ELSE
151 BEGIN
152 RAISERROR (@cmd, 0, 1) WITH NOWAIT;
153 EXEC sp_executesql @cmd;
154 END
155 END
156 ELSE
157 BEGIN
158 SET @msg = 'ERROR: ' + @NewFileGroupName + ' does not exist!';
159 RAISERROR (@msg, 0, 1) WITH NOWAIT;
160 END
161END
162ELSE
163BEGIN
164 SET @msg = 'ERROR: ' + @OldFileGroupName + ' does not exist!';
165 RAISERROR (@msg, 0, 1) WITH NOWAIT;
166END