Alle Schlüssel in allen Datenbanken neu organisieren (MSSQL > 2004)
Ja richtig, eigentlich müsste es heißen alle Indizes, aber so kleinlich bin ich gerade mal nicht.
Das Ganze pack ich jetzt in eine Stored Procedure und lass es jede Nacht einmal laufen.
DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 90 DECLARE DatabaseCursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model','distribution') 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''' EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN RAISERROR( @Table,0,0) WITH NOWAIT SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd) FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor