I need help creating a while loop that loops through all of the records in a table variable, starting with row #1 (identity #1)


I need assistance creating a while loop that will loop through all the records in my table variable.

 DECLARE @Index_Table TABLE( 
  ID int identity(1,1)
  , Database_id smallint null
  , Object_id int null
  , Index_id int null
  , Table_Name sysname not null
  , Index_Name sysname  null
  , Type tinyint null
  , Type_desc nvarchar(60) null
  , Avg_fragmentation_in_percent float null
  , Fill_factor tinyint null
  INSERT @INdex_Table (Database_id
     , object_ID 
     , Table_Name 
     , Index_Name
     , Index_ID
     , Type
     , Type_desc
     , Avg_fragmentation_in_percent
     , Fill_factor)
 SELECT ps.database_id
 , ps.object_ID
 , so.name as table_name
 , b.name as index_name
 , ps.index_id
 , b.type
 , b.type_desc
 , ps.avg_fragmentation_in_percent
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
     INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
     INNER JOIN sys.objects so ON b.object_id = so.object_id AND ps.index_id = b.index_id
 WHERE ps.database_id = DB_ID() 
 select * from @Index_Table
more ▼

asked May 03, 2012 at 04:11 PM in Default

avatar image

338 20 22 27

Just out of curiosity, what is the ultimate goal that you wish to accomplish with this data? Do you need to process things row by row or would a SET based solution be an option?

May 03, 2012 at 04:22 PM JohnM

Hi John, I am trying to run the loop row by row so I could look at each index and so later I can determine whether it needs to be rebuilt or not

May 03, 2012 at 04:49 PM tombiernacki

Thanks for the answer. Forgive me, what are you using for your decision if the index needs to be rebuilt? There are a number of home grown solutions out there that will do this for you, usually based on fragmentation levels.

Check out Michelle Ufford (http://sqlfool.com/2011/06/index-defrag-script-v4-1/)

and Ole Hallegren (http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html)

Both are excellent solutions that should accomplish what your looking for.

Attempting to do this manually will most likely become very time consuming, depending on the size/number of your tables and indexes.

Just a suggestion.

Anyway, @TimothyAWiseman has given you a couple of suggestions. I would try to stay away from the cursor.

Hope this helps!

May 03, 2012 at 05:24 PM JohnM

Thanks for all the help

May 03, 2012 at 06:29 PM tombiernacki

@JohnM Just to be clear, I would try to stay away from the cursor too. I just try to be reasonably complete and it would provide the required functionality. Also, I'll second Ola Hallegren's script for that purpose. It works quite nicely.

May 03, 2012 at 06:50 PM TimothyAWiseman
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

There are a few approaches, depending on your ultimate goal.

First, as JohnM said in his comment, look for a set based solution, you may not need a while loop at all.

If you must use a while loop, but you will only use that table variable to fuelt hat one while loop, you can have it loop while there are rows in the table, select one row, and then delete that row at the end of the loop.

Another way is to add a row-number column to the table variable and then use a counter. The counter will identify the row to select next and give you the conditional to find that row from the table and the loop can be set to terminate when your counter exceeds the number of rows in the table.

And finally (and this is probably the worst option, but for completeness....) you could use a cursor.

more ▼

answered May 03, 2012 at 04:43 PM

avatar image

15.6k 22 51 38

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 03, 2012 at 04:11 PM

Seen: 529 times

Last Updated: May 03, 2012 at 06:55 PM

Copyright 2016 Redgate Software. Privacy Policy