SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild Index Script by srihari nandamuri


Rebuild Index Script by srihari nandamuri

Author
Message
srihari nandamuri
srihari nandamuri
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 60
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
noeld
noeld
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21760 Visits: 2048
Use google there are a ton of scripts to do that. Don't try to discover the wheel.


* Noel
DHeath
DHeath
SSC Eights!
SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

Group: General Forum Members
Points: 841 Visits: 650
look in scripts area to add more or find others.

-Darryl
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search