[SQL Server] Index nouvelle réorganisation
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @readOnly BIT;
DECLARE @timeStart DATETIME;
DECLARE @timeEND DATETIME;
DECLARE DatabaseCursor CURSOR FOR
SELECT name,
is_read_only
FROM master.sys.databases
WHERE database_id > 4 -- Only user databases
ORDER BY name
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database, @readOnly
WHILE @@FETCH_STATUS = 0
BEGIN
-- turn off read-only if read only
IF @readOnly = 1
BEGIN
SET @cmd = 'ALTER DATABASE ' + @Database
+ ' SET READ_WRITE WITH NO_WAIT;'
EXEC(@cmd);
END
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM ['
+ @Database
+ '].INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''
-- create table cursor
SET @timeStart = Getdate();
PRINT 'START REORGANIZE ALL INDEXES FOR '
+ @Database + ':'
+ CONVERT(VARCHAR(24), @timeStart, 121);
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @Table
+ ' REORGANIZE;'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
SET @timeEnd = Getdate();
PRINT 'END REORGANIZE ALL INDEXES FOR '
+ @Database + ':'
+ CONVERT(VARCHAR(24), @timeStart, 121);
PRINT 'TIME TAKEN FOR ' + @Database + ' = '
+ CONVERT(VARCHAR(100), Datediff(ss, @timeStart, @timeEnd))
+ 'secs';
-- set DB back to read-only if read only
IF @readOnly = 1
BEGIN
SET @cmd = 'ALTER DATABASE ' + @Database
+ ' SET READ_ONLY WITH NO_WAIT;'
EXEC(@cmd);
END
FETCH NEXT FROM DatabaseCursor INTO @Database, @readOnly
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor