· 6 years ago · Mar 08, 2019, 05:38 PM
1IF OBJECT_ID('tempdb..##MoveIndexToFileGroup') IS NOT NULL
2BEGIN
3 DROP PROC ##MoveIndexToFileGroup
4END
5
6GO
7
8CREATE PROC ##MoveIndexToFileGroup (
9 @DBName sysname,
10 @SchemaName sysname = 'dbo',
11 @ObjectNameList Varchar(Max),
12 @IndexName sysname = null,
13 @FileGroupName varchar(100)
14) WITH RECOMPILE
15
16AS
17
18BEGIN
19
20 SET NOCOUNT ON;
21
22 DECLARE @IndexSQL NVarchar(Max)
23 DECLARE @IndexKeySQL NVarchar(Max)
24 DECLARE @IncludeColSQL NVarchar(Max)
25 DECLARE @FinalSQL NVarchar(Max)
26
27 DECLARE @CurLoopCount Int
28 DECLARE @MaxLoopCount Int
29 DECLARE @StartPos Int
30 DECLARE @EndPos Int
31
32 DECLARE @ObjectName sysname
33 DECLARE @IndName sysname
34 DECLARE @IsUnique Varchar(10)
35 DECLARE @Type Varchar(25)
36 DECLARE @IsPadded Varchar(5)
37 DECLARE @IgnoreDupKey Varchar(5)
38 DECLARE @AllowRowLocks Varchar(5)
39 DECLARE @AllowPageLocks Varchar(5)
40 DECLARE @FillFactor Int
41 DECLARE @ExistingFGName Varchar(Max)
42 DECLARE @FilterDef NVarchar(Max)
43
44 DECLARE @ErrorMessage NVARCHAR(4000)
45 DECLARE @SQL nvarchar(4000)
46 DECLARE @RetVal Bit
47
48 DECLARE @ObjectList Table(Id Int Identity(1,1),ObjectName sysname)
49
50 DECLARE @WholeIndexData TABLE (
51 ObjectName SYSNAME
52 ,IndexName SYSNAME
53 ,Is_Unique BIT
54 ,Type_Desc VARCHAR(25)
55 ,Is_Padded BIT
56 ,[Ignore_Dup_Key] BIT
57 ,[Allow_Row_Locks] BIT
58 ,[Allow_Page_Locks] BIT
59 ,Fill_Factor INT
60 ,Is_Descending_Key BIT
61 ,ColumnName SYSNAME
62 ,Is_Included_Column BIT
63 ,FileGroupName VARCHAR(MAX)
64 ,Has_Filter BIT
65 ,Filter_Definition NVARCHAR(MAX)
66 ,key_ordinal TINYINT
67 )
68
69 DECLARE @DistinctIndexData TABLE (
70 Id INT IDENTITY(1, 1)
71 ,ObjectName SYSNAME
72 ,IndexName SYSNAME
73 ,Is_Unique BIT
74 ,Type_Desc VARCHAR(25)
75 ,Is_Padded BIT
76 ,[Ignore_Dup_Key] BIT
77 ,[Allow_Row_Locks] BIT
78 ,[Allow_Page_Locks] BIT
79 ,Fill_Factor INT
80 ,FileGroupName VARCHAR(Max)
81 ,Has_Filter BIT
82 ,Filter_Definition NVARCHAR(Max)
83 )
84
85-------------Validate arguments----------------------
86
87 IF(@DBName IS NULL)
88 BEGIN
89 SELECT @ErrorMessage = 'Database Name must be supplied.'
90 GOTO ABEND
91 END
92
93 IF(@ObjectNameList IS NULL)
94 BEGIN
95 SELECT @ErrorMessage = 'Table or View Name(s) must be supplied.'
96 GOTO ABEND
97 END
98
99 IF(@FileGroupName IS NULL)
100 BEGIN
101 SELECT @ErrorMessage = 'FileGroup Name must be supplied.'
102 GOTO ABEND
103 END
104
105 --Check for the existence of the Database
106 IF NOT EXISTS(SELECT Name FROM sys.databases where Name = @DBName)
107 BEGIN
108 SET @ErrorMessage = 'The specified Database does not exist'
109 GOTO ABEND
110 END
111
112 --Check for the existence of the Schema
113 IF (upper(@SchemaName) <> 'DBO')
114 BEGIN
115 SET @SQL = 'SELECT @RetVal = COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.schemas WHERE name = ''' + @SchemaName + ''''
116
117 BEGIN TRY
118 EXEC sp_executesql @SQL, N'@RetVal Bit OUTPUT', @RetVal OUTPUT
119 END TRY
120 BEGIN CATCH
121 SELECT @ErrorMessage = ERROR_MESSAGE()
122 GOTO ABEND
123 END CATCH
124
125 IF (@RetVal = 0)
126 BEGIN
127 SELECT @ErrorMessage = 'No Schema with the name ' + @SchemaName + ' exists in the Database ' + @DBName
128 GOTO ABEND
129 END
130 END
131
132 --CHECK FOR THE EXISTENCE OF THE FILEGROUP . Removed for now, will update to look for partition schemes later
133 --SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.filegroups WHERE name = ''' + @FileGroupName + ''''
134 --BEGIN TRY
135 -- EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT
136 --END TRY
137 --BEGIN CATCH
138 -- SELECT @ErrorMessage = ERROR_MESSAGE()
139 -- GOTO ABEND
140 --END CATCH
141
142 --IF(@RetVal = 0)
143 -- BEGIN
144 -- SELECT @ErrorMessage = 'No FileGroup with the name ' + @FileGroupName + ' exists in the Database ' + @DBName
145 -- GOTO ABEND
146 -- END
147
148----------Get the objects from the concatenated list----------------------------------------------------
149
150SET @StartPos = 0
151SET @EndPos = 0
152
153WHILE(@EndPos >= 0)
154BEGIN
155
156 SELECT @EndPos = CHARINDEX(',',@ObjectNameList,@StartPos)
157 IF(@EndPos = 0) --Means, separator is not found
158 BEGIN
159 INSERT INTO @ObjectList
160 SELECT SUBSTRING(@ObjectNameList,@StartPos,(LEN(@ObjectNameList) - @StartPos)+1)
161
162 BREAK
163 END
164
165 INSERT INTO @ObjectList
166 SELECT SUBSTRING(@ObjectNameList,@StartPos,(@EndPos - @StartPos))
167
168 SET @StartPos = @EndPos + 1
169
170END
171
172-------------Check for the validity of all the Objects----------------------
173
174SET @StartPos = 1
175SELECT @EndPos = COUNT(*) FROM @ObjectList
176
177WHILE(@StartPos <= @EndPos)
178BEGIN
179
180 SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos
181
182 --CHECK FOR EXISTENCE OF THE OBJECT
183 SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Objects WHERE type IN (''U'',''V'') AND name = ''' + @ObjectName + ''''
184 BEGIN TRY
185 EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
186 END TRY
187 BEGIN CATCH
188 SELECT @ErrorMessage = ERROR_MESSAGE()
189 GOTO ABEND
190 END CATCH
191
192 IF(@RetVal = 0)
193 BEGIN
194 SELECT @ErrorMessage = 'No Table or View with the name ' + @ObjectName + ' exists in the Database ' + @DBName
195 GOTO ABEND
196 END
197
198 --Check for existence of Index
199 IF(@IndexName IS NOT NULL)
200 BEGIN
201 SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Indexes si INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Objects so '
202 SET @SQL = @SQL + ' ON si.Object_Id = so.Object_Id WHERE so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25))
203 SET @SQL = @SQL + ' AND so.name = ''' + @ObjectName + ''' AND si.name = ''' + @IndexName + ''''
204
205 BEGIN TRY
206 EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT
207 END TRY
208 BEGIN CATCH
209 SELECT @ErrorMessage = ERROR_MESSAGE()
210 GOTO ABEND
211 END CATCH
212
213 IF(@RetVal = 0)
214 BEGIN
215 SELECT @ErrorMessage = 'No Index with the name ' + @IndexName + ' exists on the Object ' + @ObjectName
216 GOTO ABEND
217 END
218 END
219
220 SET @StartPos = @StartPos + 1
221END
222
223-------------Loop till all the Objects are processed----------------------
224
225SET @StartPos = 1
226SELECT @EndPos = COUNT(*) FROM @ObjectList
227
228WHILE(@StartPos <= @EndPos)
229BEGIN
230
231 SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos
232
233 -------------Build the SQL to get the index data based on the inputs provided----------------------
234
235 Print @ObjectName
236
237 SET @IndexSQL =
238 'SELECT so.Name as ObjectName, si.Name as IndexName,si.Is_Unique,si.Type_Desc'
239 + ',si.Is_Padded,si.Ignore_Dup_Key,si.Allow_Row_Locks,si.Allow_Page_Locks,si.Fill_Factor,sic.Is_Descending_Key'
240 + ',sc.Name as ColumnName,sic.Is_Included_Column,coalesce(sf.Name, ps.Name) as FileGroupName,'+ CASE WHEN @@VERSION LIKE '%Server 2005%' THEN '0 as Has_Filter, N'''' as Filter_Definition' ELSE 'si.Has_Filter,si.Filter_Definition' END +',sic.Key_Ordinal FROM '
241 + QUOTENAME(@DBName) + '.sys.Objects so INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Indexes si ON so.Object_Id = si.Object_id LEFT JOIN '
242 + QUOTENAME(@DBName) + '.sys.FileGroups sf ON sf.Data_Space_Id = si.Data_Space_Id LEFT JOIN '
243 + QUOTENAME(@DBName) + '.sys.Partition_schemes ps ON ps.Data_Space_Id = si.Data_Space_Id INNER JOIN '
244 + QUOTENAME(@DBName) + '.sys.Index_columns sic ON si.Object_Id = sic.Object_Id AND si.Index_id = sic.Index_id INNER JOIN '
245 + QUOTENAME(@DBName) + '.sys.Columns sc ON sic.Column_Id = sc.Column_Id and sc.Object_Id = sic.Object_Id '
246 + ' WHERE so.Name = ''' + @ObjectName + ''''
247 + ' AND so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25)) -- + ' AND si.Type_Desc = ''NONCLUSTERED'' '
248
249 PRINT @IndexSQL
250
251 IF(@IndexName IS NOT NULL)
252 BEGIN
253 SET @IndexSQL = @IndexSQL + ' AND si.Name = ''' + @IndexName + ''''
254 END
255
256 SET @IndexSQL = @IndexSQL + ' ORDER BY ObjectName, IndexName, sic.Key_Ordinal'
257
258 --PRINT @IndexSQL
259
260 -------------INSERT THE INDEX DATA INTO A VARIABLE----------------------
261
262 BEGIN TRY
263 INSERT INTO @WholeIndexData
264 EXEC sp_executesql @IndexSQL
265 END TRY
266 BEGIN CATCH
267 SELECT @ErrorMessage = ERROR_MESSAGE()
268 GOTO ABEND
269 END CATCH
270
271 --Check if any indexes are there on the object. Otherwise exit
272 IF (SELECT COUNT(*) FROM @WholeIndexData) = 0
273 BEGIN
274 SELECT 'Object does not have any nonclustered indexes to move'
275 GOTO FINAL
276 END
277
278 -------------Get the distinct index rows in to a variable----------------------
279
280INSERT INTO @DistinctIndexData
281SELECT DISTINCT
282 ObjectName
283 ,IndexName
284 ,Is_Unique
285 ,Type_Desc
286 ,Is_Padded
287 ,[Ignore_Dup_Key]
288 ,[Allow_Row_Locks]
289 ,[Allow_Page_Locks]
290 ,Fill_Factor
291 ,FileGroupName
292 ,Has_Filter
293 ,Filter_Definition
294FROM @WholeIndexData
295WHERE ObjectName = @ObjectName;
296
297 SELECT @CurLoopCount = Min(Id), @MaxLoopCount = Max(Id) FROM @DistinctIndexData WHERE ObjectName = @ObjectName
298
299 --SELECT @CurLoopCount, @MaxLoopCount
300
301 -------------Loop till all the indexes are processed----------------------
302
303 WHILE(@CurLoopCount <= @MaxLoopCount)
304 BEGIN
305
306 SET @IndexKeySQL = ''
307 SET @IncludeColSQL = ''
308
309 -------------Get the current index row to be processed----------------------
310 SELECT
311 @IndName = IndexName
312 ,@Type = Type_Desc
313 ,@ExistingFGName = FileGroupName
314 ,@IsUnique = CASE WHEN Is_Unique = 1 THEN 'UNIQUE ' ELSE '' END
315 ,@IsPadded = CASE WHEN Is_Padded = 0 THEN 'OFF,' ELSE 'ON,' END
316 ,@IgnoreDupKey = CASE WHEN Ignore_Dup_Key = 0 THEN 'OFF,' ELSE 'ON,' END
317 ,@AllowRowLocks = CASE WHEN Allow_Row_Locks = 0 THEN 'OFF,' ELSE 'ON,' END
318 ,@AllowPageLocks = CASE WHEN Allow_Page_Locks = 0 THEN 'OFF,' ELSE 'ON,' END
319 ,@FillFactor = CASE WHEN Fill_Factor = 0 THEN 100 ELSE Fill_Factor END
320 ,@FilterDef = CASE WHEN Has_Filter = 1 THEN (' WHERE ' + Filter_Definition) ELSE '' END
321 FROM @DistinctIndexData
322 WHERE Id = @CurLoopCount
323
324 -------------Check if the index is already not part of that FileGroup----------------------
325
326 IF(@ExistingFGName = @FileGroupName)
327 BEGIN
328 PRINT 'Index ' + @IndName + ' is NOT moved as it is already part of the FileGroup ' + @FileGroupName + '.'
329 SET @CurLoopCount = @CurLoopCount + 1
330 CONTINUE
331 END
332
333 ------- Construct the Index key string along with the direction--------------------
334 SELECT @IndexKeySQL = CASE
335 WHEN @IndexKeySQL = ''
336 THEN (
337 @IndexKeySQL + QUOTENAME(ColumnName) + CASE
338 WHEN Is_Descending_Key = 0
339 THEN ' ASC'
340 ELSE ' DESC'
341 END
342 )
343 ELSE (
344 @IndexKeySQL + ',' + QUOTENAME(ColumnName) + CASE
345 WHEN Is_Descending_Key = 0
346 THEN ' ASC'
347 ELSE ' DESC'
348 END
349 )
350 END
351 FROM @WholeIndexData
352 WHERE ObjectName = @ObjectName
353 AND IndexName = @IndName
354 AND Is_Included_Column = 0
355 ORDER BY key_ordinal ASC
356
357
358 --PRINT @IndexKeySQL
359
360 ------ Construct the Included Column string --------------------------------------
361 SELECT
362 @IncludeColSQL =
363 CASE
364 WHEN @IncludeColSQL = '' THEN (@IncludeColSQL + QUOTENAME(ColumnName))
365 ELSE (@IncludeColSQL + ',' + QUOTENAME(ColumnName))
366 END
367 FROM @WholeIndexData
368 WHERE ObjectName = @ObjectName
369 AND IndexName = @IndName
370 AND Is_Included_Column = 1
371 ORDER BY key_ordinal ASC
372
373 --PRINT @IncludeColSQL
374
375 -------------Construct the final Create Index statement----------------------
376 SELECT
377 @FinalSQL = 'CREATE ' + @IsUnique + @Type + ' INDEX ' + QUOTENAME(@IndName)
378 + ' ON ' + QUOTENAME(@DBName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)
379 + '(' + @IndexKeySQL + ') '
380 + CASE WHEN LEN(@IncludeColSQL) <> 0 THEN 'INCLUDE(' + @IncludeColSQL + ') ' ELSE '' END
381 + @FilterDef
382 + ' WITH ('
383 + 'PAD_INDEX = ' + @IsPadded
384 + 'IGNORE_DUP_KEY = ' + @IgnoreDupKey
385 + 'ALLOW_ROW_LOCKS = ' + @AllowRowLocks
386 + 'ALLOW_PAGE_LOCKS = ' + @AllowPageLocks
387 + 'SORT_IN_TEMPDB = OFF,'
388 + 'DROP_EXISTING = ON,'
389 + 'ONLINE = OFF,'
390 + 'FILLFACTOR = ' + CAST(@FillFactor AS Varchar(3))
391 + ') ON ' + @FileGroupName
392
393 --PRINT @FinalSQL
394
395 -------------Execute the Create Index statement to move to the specified filegroup----------------------
396 BEGIN TRY
397 PRINT @FinalSQL;
398 EXEC sp_executesql @FinalSQL
399 END TRY
400 BEGIN CATCH
401 SELECT @ErrorMessage = ERROR_MESSAGE()
402 GOTO ABEND
403 END CATCH
404 PRINT 'Index ' + @IndName + ' on Object ' + @ObjectName + ' is moved successfully.'
405
406 SET @CurLoopCount = @CurLoopCount + 1
407
408 END
409
410 SET @StartPos = @StartPos + 1
411END
412 SELECT 'The procedure completed successfully.'
413 RETURN
414
415ABEND:
416 RAISERROR (@ErrorMessage, 16, 1);
417
418FINAL:
419 RETURN
420END
421
422GO