Technical Article

Rebuild indexes, update stats and shrink

,

For each user database  rebuild indexes, update statistics and shrink.  The script will use the system catalog to produce a list of databases. If you want to excluce a database add the excluded db name to the 'not in' list.  You can also adjust the target fillfactor.

/* 

Function: For each user database  rebuild indexes, update statistics and shrink.  

Instructions: Run against master, the script will use the system catalog to produce a list of databases
If you want to excluce a database add the excluded db name to the 'not in' list below.  
You can also adjust the target fillfactor below.

Questions: cjm@integer.org
Updates: http://www.integer.org/cjm/files/indexstatsshrink.sql

copyright (C) 2003 Cameron Michelis copying and redistribution of this file is permitted provided this notice and the above comments are preserved.
*/
Set quoted_identifier off
use master
go


DECLARE @fillfactor varchar(2)
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE @dataname varchar(30)
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases
        WHERE name not in ('master', 'pubs', 'tempdb', 'model', 'northwind')
/* Variable Initialization */select @fillfactor = "0"-- Set Fill factor here
-- Note "0" will use original fillfactor.
/* End Variable Initialization */OPEN datanames_cursor

  FETCH NEXT FROM datanames_cursor INTO @dataname

  WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status = -2)
        BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
          CONTINUE
        END
SELECT @dataname_header = "Database " + RTRIM(UPPER(@dataname))
      PRINT " "
PRINT @dataname_header
      PRINT " "
EXEC ("USE " + @dataname + " DECLARE tnames_cursor CURSOR FOR SELECT name from sysobjects where type = 'U'")
Select @dataname_header = RTRIM(UPPER(@dataname))
Exec ("Use " + @dataname) 
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
        BEGIN
          IF (@@fetch_status = -2)            
BEGIN
              FETCH NEXT FROM tnames_cursor INTO @tablename
              CONTINUE
            END
    SELECT @tablename_header = "  Updating " + RTRIM(UPPER(@tablename))
PRINT ""
          PRINT @tablename_header
EXEC ("USE " + @dataname + " DBCC DBREINDEX (" + @tablename + "," + "''" + "," + @fillfactor + ")")
EXEC ("USE " + @dataname + " UPDATE STATISTICS " + @tablename)
FETCH NEXT FROM tnames_cursor INTO @tablename
        END
DEALLOCATE tnames_cursor
EXEC("DBCC SHRINKDATABASE (" + @dataname + ", TRUNCATEONLY)")
      FETCH NEXT FROM datanames_cursor INTO @dataname
      END



DEALLOCATE datanames_cursor
PRINT ""
PRINT " "
PRINT "Indexing and shrinkage complete for All User Databases"

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating