January 27, 2009 at 8:39 am
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
January 27, 2009 at 9:18 am
Use google there are a ton of scripts to do that. Don't try to discover the wheel.
* Noel
January 27, 2009 at 11:06 am
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