Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild Index Script by srihari nandamuri Expand / Collapse
Author
Message
Posted Tuesday, January 27, 2009 8:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Tuesday, January 27, 2009 9:18 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030
Use google there are a ton of scripts to do that. Don't try to discover the wheel.



* Noel
Post #644253
Posted Tuesday, January 27, 2009 11:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:38 PM
Points: 209, Visits: 642
look in scripts area to add more or find others.

-Darryl
Post #644396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse