InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 04:59:52 GMT20http://www.sqlservercentral.com/Forums/Topic644205-146-1.aspxlook in scripts area to add more or find others.-DarrylTue, 27 Jan 2009 11:06:05 GMTDHeathhttp://www.sqlservercentral.com/Forums/Topic644205-146-1.aspxUse google there are a ton of scripts to do that. Don't try to discover the wheel.Tue, 27 Jan 2009 09:18:14 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic644205-146-1.aspxRebuilding 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 dataDECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @SqlQuery NVARCHAR(500) DECLARE @fill_factor INT declare @dbid intdeclare @tableid intSET @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 =1while @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 +1enddrop table #databasesselect * from #AlterIndex_Scriptdrop table #AlterIndex_ScriptTue, 27 Jan 2009 08:39:50 GMTsrihari nandamuri