asked
I need help creating a while loop that loops through all of the records in a table variable, starting with row #1 (identity #1)
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
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!
@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.
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
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.
6 People are following this question.
