Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Loop thru Indexed

Author  Topic 

bconner
Starting Member

48 Posts

Posted - 2013-01-31 : 15:08:59
Is there a way to loop thru Indexes on a given table to do a recreate instead of using an index name?

Brian

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-01-31 : 15:45:43
ALTER INDEX ALL On <Table Name> REBUILD;
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-01-31 : 15:52:54
Hello, I found this somewhere and tweaked it to meet my needs.
-- Create needed table
USE YourDatabase
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fragmentation]') AND type in (N'U'))
DROP TABLE Fragmentation;

CREATE TABLE [dbo].[Fragmentation](
DBName varchar(128),
TableName varchar(128),
ExecuteCode varchar(1000),
avg_frag_percent decimal(18, 4)
);
GO

TRUNCATE TABLE YourDatabase.dbo.Fragmentation;

DECLARE @strSQL NVARCHAR(2000)
SET @strSQL = 'IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'', '
+ '''ReportServer$DB'', ''distribution'') RETURN; '
+ 'USE ?; '
+ 'DECLARE @Did INT; '
+ 'SELECT @Did = dbid FROM sys.sysdatabases WHERE name = DB_NAME(); '
+ 'INSERT INTO YourDatabase.dbo.Fragmentation '
+ 'SELECT ''?'' AS DBName, t.name AS TableName, '
+ 'CASE WHEN avg_fragmentation_in_percent < 30 '
+ 'THEN ''ALTER INDEX ['' + s.name + ''] ON ['' + t.name + ''] REORGANIZE; --'' '
+ 'ELSE ''ALTER INDEX ['' + s.name + ''] ON ['' + t.name + ''] REBUILD; --'' '
+ 'END AS ExecuteCode, avg_fragmentation_in_percent '
+ 'FROM sys.dm_db_index_physical_stats (@Did, NULL, NULL , NULL, N''LIMITED'') d '
+ 'INNER JOIN sysindexes s ON OBJECT_ID = s.id and d.index_id = s.indid '
+ 'INNER JOIN sys.tables t ON s.id = t.object_id '
+ 'WHERE avg_fragmentation_in_percent > 30 And '
+ 's.name IS NOT NULL And page_count > 1000 '
+ 'ORDER BY DBName, s.name DESC; '
--print @strSQL

EXECUTE sp_MSForEachdB @strSQL

SELECT * FROM YourDatabase.dbo.Fragmentation ORDER BY DBName, TableName, ExecuteCode DESC;


djj
Go to Top of Page

bconner
Starting Member

48 Posts

Posted - 2013-01-31 : 16:30:43
Thanks russell and djj55 this is exactly what I am looking for.

Brian
Go to Top of Page
   

- Advertisement -