/*
Script for creating reindex of every db and table
in an SQL server
USAGE: Execute the script and copy the result
Run the result as a job when the server
load is low (EG. at night after backup).
IF there are many big databases - split
and run parts on different times.
*/
-- Use Master
USE master
GO
DECLARE @cDatabasename nvarchar(128)
DECLARE @cTabelname nvarchar(128)
DECLARE @string nvarchar(128)
-- a outer loop to find all databases
-- exept master, model, msdb
-- create a cursor
DECLARE curDatabaser CURSOR FOR
SELECT [name] FROM sysdatabases WHERE dbid > 4
-- open cursor
OPEN curDatabaser
SET @string = @@servername
PRINT '-- Valid for SQL server ' + @string
SET @string = GETDATE()
PRINT '-- Date/Time ' + @string
PRINT ''
-- Read this first!
FETCH NEXT FROM curDatabaser
INTO @cDatabasename
-- read as long as there is data
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string = 'USE ' + @cDatabasename
PRINT @string
SET @string = 'exec sp_msforeachtable @command1=''dbcc dbreindex(''''?'''') '''
PRINT @string
SET @string = 'GO'
PRINT @string
PRINT ''
FETCH NEXT FROM curDatabaser
INTO @cDatabasename
END
-- close and clean
CLOSE curDatabaser
DEALLOCATE curDatabaser
No comments:
Post a Comment