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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
352 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  
 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.06 seconds. Powered By: Snitz Forums 2000