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

Rebuid Index Script by Srihari Nandamuri Expand / Collapse
Author
Message
Posted Monday, January 26, 2009 6:16 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
Below query is to Rebuild Index(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')

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 #643379
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse