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