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

Question

Hello

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
,b.fill_factor
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() 
ORDER BY ps.OBJECT_ID 

select * from @Index_Table

Answer 1

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.

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

Follow this question

Related Questions