SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Loop thru Indexed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bconner
Starting Member

USA
48 Posts

Posted - 01/31/2013 :  15:08:59  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 01/31/2013 :  15:45:43  Show Profile  Visit russell's Homepage  Reply with Quote
ALTER INDEX ALL On <Table Name> REBUILD;
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
341 Posts

Posted - 01/31/2013 :  15:52:54  Show Profile  Reply with Quote
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

USA
48 Posts

Posted - 01/31/2013 :  16:30:43  Show Profile  Reply with Quote
Thanks russell and djj55 this is exactly what I am looking for.

Brian
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000