DECLARE @Database VARCHAR(255); DECLARE @Table VARCHAR(255); DECLARE @cmd NVARCHAR(500); DECLARE @fillfactor INT; SET @fillfactor = 80; DECLARE DatabaseCursor CURSOR FOR SELECT name FROM sys.databases WITH ( NOLOCK ) WHERE name NOT IN ( 'master', 'msdb', 'tempdb', 'model', 'distribution' ) AND state_desc = 'ONLINE' ORDER BY 1; OPEN DatabaseCursor; FETCH NEXT FROM DatabaseCursor INTO @Database; WHILE @@FETCH_STATUS = 0 BEGIN 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 EXEC ( @cmd ); OPEN TableCursor; FETCH NEXT FROM TableCursor INTO @Table; WHILE @@FETCH_STATUS = 0 BEGIN IF ( @@MICROSOFTVERSION / POWER(2, 24) >= 9 ) BEGIN -- SQL 2005 or higher command SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @fillfactor) + ')'; EXEC ( @cmd ); END; ELSE BEGIN -- SQL 2000 command DBCC DBREINDEX(@Table, ' ', @fillfactor); END; FETCH NEXT FROM TableCursor INTO @Table; END; CLOSE TableCursor; DEALLOCATE TableCursor; FETCH NEXT FROM DatabaseCursor INTO @Database; END; CLOSE DatabaseCursor; DEALLOCATE DatabaseCursor;
Wednesday, July 26, 2017
Rebuild All Index of All Table of All Database - SQL Server
To Rebuild all Indexes of all Tables of all databases can be done using this code.
Subscribe to:
Posts (Atom)