Rebuild Index Script by srihari nandamuri

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Rebuild Index Script by srihari nandamuri

Rebuild Index Script by srihari nandamuri

Rate Topic

Display Mode

Topic Options

Author

Message

srihari nandamuri

Posted Tuesday, January 27, 2009 8:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, April 19, 2012 6:44 AM
Points: 273, Visits: 56

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

Post #644205