Search

Thursday, July 28, 2011

Script for creating reindex of every db and table in a SQL server(instance)

/*
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