· 8 years ago · May 17, 2018, 12:22 AM
1USE [LogBase_PM]
2GO
3
4IF EXISTS (SELECT '*' FROM sys.objects
5 WHERE object_id = OBJECT_ID(N'SP_Rebuild_Indexes') AND type IN ( N'P'))
6BEGIN
7 DROP PROC SP_Rebuild_Indexes
8END
9GO
10
11IF type_id('[TablesToRebuildIndexes]') IS NOT NULL
12 DROP TYPE TablesToRebuildIndexes;
13GO
14
15CREATE TYPE TablesToRebuildIndexes
16AS TABLE
17(
18 TableName nvarchar(255)
19)
20GO
21
22CREATE PROC SP_Rebuild_Indexes
23 @dbName NVARCHAR(128),
24 @schema NVARCHAR(128),
25 @tableNames AS TablesToRebuildIndexes READONLY
26AS
27BEGIN
28 IF (SELECT COUNT(*) FROM @tableNames) = 0
29 RETURN;
30
31 DECLARE @startDate DATETIME;
32
33 DECLARE @Source NVARCHAR(20),
34 @IndexMessage NVARCHAR(MAX),
35 @TableMessageStart NVARCHAR(MAX),
36 @TableMessageEnd NVARCHAR(MAX),
37 @ExceptionMessage NVARCHAR(MAX),
38 @ClassName NVARCHAR(255),
39 @Method NVARCHAR(255),
40 @Started TINYINT,
41 @Failed TINYINT,
42 @Finished TINYINT;
43
44 SET @Source = 'DB(' + @dbName + ')';
45 SET @ClassName = 'Stored Procedure';
46 SET @Method = 'SP_Rebuild_Indexes';
47 SET @Started = 1;
48 SET @Failed = 2;
49 SET @Finished = 3;
50
51 DECLARE @tableName NVARCHAR(255);
52 DECLARE tables_cursor CURSOR READ_ONLY
53 FOR SELECT * FROM @tableNames;
54
55 OPEN tables_cursor
56 FETCH NEXT FROM tables_cursor
57 INTO @tableName
58 WHILE @@FETCH_STATUS = 0
59 BEGIN
60 IF (SELECT OBJECT_ID(@dbName+'.' + @schema + '.' + @tableName)) IS NOT NULL
61 BEGIN
62 SET @startDate = GETDATE();
63
64 SET @TableMessageStart = 'Table ' + @tableName + ' rebuilding indexes started';
65 INSERT INTO [LogBase_PM].[dbo].[LogStackInfo] (Source, Message, ClassName, Method, CreatedDate, ServiceLogType) VALUES(@Source, @TableMessageStart, @ClassName, @Method, GETDATE(), @Started);
66
67
68 DECLARE @iname NVARCHAR(255), @oid INT, @iid TINYINT, @frag FLOAT
69 DECLARE @alter NVARCHAR(MAX);
70
71 DECLARE indexes_cursor CURSOR READ_ONLY
72 FOR SELECT DISTINCT I.name, IPS.object_id, IPS.index_id, MAX(IPS.avg_fragmentation_in_percent)
73 FROM sys.dm_db_index_physical_stats(DB_ID(@dbName), OBJECT_ID(@dbName+'.' + @schema + '.' + @tableName), NULL, NULL , 'DETAILED') AS IPS
74 JOIN sys.indexes AS I ON I.object_id = IPS.object_id AND I.index_id = IPS.index_id
75 GROUP BY IPS.index_id, I.name, IPS.object_id
76
77 OPEN indexes_cursor
78 FETCH NEXT FROM indexes_cursor
79 INTO @iname, @oid, @iid, @frag
80 WHILE @@FETCH_STATUS = 0
81 BEGIN
82 BEGIN TRY
83 SET @alter = N'ALTER INDEX [' + @iname + N'] ON [' + @schema + N'].[' + @tableName + N'] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)';
84 EXEC(@alter)
85
86 SET @IndexMessage = N'Indx ' + CAST(@iid AS NVARCHAR) + N', ' + @iname + N' REBUILT';
87 INSERT INTO [LogBase_PM].[dbo].[LogStackInfo] (Source, Message, ClassName, Method, CreatedDate, ServiceLogType) VALUES(@Source, @IndexMessage, @ClassName, @Method, GETDATE(), @Finished);
88 END TRY
89 BEGIN CATCH
90 SET @ExceptionMessage = 'Exception occured during rebuilding index ' + CAST(@iid AS NVARCHAR)+ ' ' + @iname + ' for table ' + @tableName + '\n' +
91 'Error message:\n' + ERROR_MESSAGE();
92 INSERT INTO [LogBase_PM].[dbo].[LogStackInfo] (Source, Message, ClassName, Method, CreatedDate, ServiceLogType) VALUES(@Source, @ExceptionMessage, @ClassName, @Method, GETDATE(), @Failed);
93 END CATCH
94
95 FETCH NEXT FROM indexes_cursor
96 INTO @iname, @oid, @iid, @frag
97 END
98 CLOSE indexes_cursor;
99 DEALLOCATE indexes_cursor;
100
101
102
103 SET @TableMessageEnd = 'Table ' + @tableName + ' were rebuilt indexes in ' + CAST(DATEDIFF(millisecond, @startDate, GETDATE()) AS NVARCHAR(255)) + 'ms';
104 INSERT INTO [LogBase_PM].[dbo].[LogStackInfo] (Source, Message, ClassName, Method, CreatedDate, ServiceLogType) VALUES(@Source, @TableMessageEnd, @ClassName, @Method, GETDATE(), @Finished);
105 END
106
107 FETCH NEXT FROM tables_cursor
108 INTO @tableName
109 END
110 CLOSE tables_cursor;
111 DEALLOCATE tables_cursor;
112END
113GO