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

tombiernacki gravatar image

338 19 20 23

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

TimothyAWiseman gravatar image

15.6k 21 23 32

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 445 times

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