how to get information about Fragmentation of index in SQL server 2005 by scripts

how to get information 开发者_如何学Pythonabout Fragmentation of index in SQL server 2005 by scripts

---

You can find it here.

---

I don't know your situation, but if you run that kind of a script on a live database you can get nasty performance problems while it is running. If some of the indexes are badly fragmented, let's say more than 40-50%, then it becomes a better alternative to rebuild it.
But you can of course make that script something like:

DECLARE cur_idx CURSOR
SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > @your_limit_for_fragmentation

OPEN CURSOR cur_idx

FETCH NEXT FROM cur_idx INTO @idx_name
WHILE @@FETCHSTATUS = 0 BEGIN
   --create dynamic sql statement 
END

--close cursor and clean up