Rebuild Index Script by srihari nandamuri

  • Rebuilding an index reorganizes the storage of the index data (and table data in the case

    of a clustered index) to remove fragmentation.

    This can improve disk performance by reducing the number of page reads required to obtain the requested data

    DECLARE @Database VARCHAR(255)

    DECLARE @Table VARCHAR(255)

    DECLARE @sqlquery NVARCHAR(500)

    DECLARE @fill_factor INT

    declare @dbid int

    declare @tableid int

    SET @fill_factor = 90

    create table #AlterIndex_Script (id int identity,Script nvarchar(400))

    create table #databases (id int identity,name varchar(100))

    insert into #databases (name)

    SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','system')

    set @dbid =1

    while @dbid <= (select count(*) from #databases)

    begin

    select @Database = name from #databases where id = @dbid

    create table #tables (id int identity,tablename varchar(500))

    SET @sqlquery = 'insert into #tables SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName

    FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

    EXEC (@SqlQuery)

    set @tableid =1

    while @tableid <=(select count(*) from #tables)

    begin

    select @Table =tablename from #tables where id =@tableid

    SET @sqlquery = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fill_factor) + ')'

    --SET @sqlquery = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD REBUILD WITH(ONLINE = ON) '

    insert into #AlterIndex_Script (script) select @sqlquery

    set @tableid =@tableid+1

    end

    drop table #tables

    set @dbid =@dbid +1

    end

    drop table #databases

    select * from #AlterIndex_Script

    drop table #AlterIndex_Script

    Comments please....

    Srihari Nandamuri

  • Use google there are a ton of scripts to do that. Don't try to discover the wheel.


    * Noel

  • look in scripts area to add more or find others.

    -Darryl

    DHeath

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply