Re index Using msforeachtable


Vasanth Kumar Sivaji

Hi friends, I have faced an interesting Scenario in rebuilding index , where I have to determine what is the fragmentation and also check weather the page count is greater than 250.

For simple rebuild using msforeachtable, we can go for

EXEC sp_MSforeachtable @command1=’print ”?” DBCC DBREINDEX (”?”, ” ”, 0)

Where it Reindex all the table in particular database.

After headbreaking workouts I came with a solution let me share it with you

create proc usp_msforeachreindex

as

begin

      DECLARE @MAXROW INT,@ROW INT = 1,@SQL VARCHAR(250)

      CREATE TABLE #TEMP1(ROWNUM INT IDENTITY(1,1),CMD NVARCHAR(MAX))

      EXEC sp_MSforeachtable ‘INSERT INTO #TEMP1(CMD) SELECT ”ALTER INDEX ALL  ON ”+OBJECT_NAME(S.object_id)+” REBUILD ;”

      FROM Sys.dm_db_index_physical_stats(db_id(),object_id(”?”) ,null,null,”DETAILED”) S

      where page_count>250 and avg_fragmentation_in_percent>40′

      SELECT   @MAXROW = MAX(ROWNUM) FROM  #TEMP1

      WHILE(@ROW <= @MAXROW)

      BEGIN

            SELECT @SQL=CMD FROM #TEMP1 WHERE ROWNUM = @ROW

            –PRINT @SQL

            EXEC (@SQL)

            SET @ROW =…

View original post 30 more words

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s