This is one of the things I love about being a DBA: we like to share what we know. I always try to teach people who work with me some things about how a database works, and a lot of other DBAs like to do the same. Because of that we have TONS of great blogs and community sites to talk about what we know, and in those places you will find a lot of great scripts and tips to make your database run better.
Today I was searching an old reindexing script I had created some time ago, that would reindex or reorganize the indexes based on fragmentation level. It turns out I lost the code, so would have to create it again. Then enter my friend google, and after a few blog posts I found a script that does just what mine did but does it BETTER. Of course I have to share it with you fellow readers, as well as the links that took me there.
The first post I found about this is here. It’s a very simple and easy to understand script. It uses SQL2000 commands, so if you are stuck with this version you may use this to reindex everything in there and not worry about that base for some time.
The second post about this subject was this one. This guy said this is “the last defrag script I’ll need” and I must say he was pretty correct in his statement. Of course there is always something new happening on database systems, new commands and new options and stuff, so he will probably need to update that script some day.
This fact led me to the original creator of the script, Michelle Ufford from SQLfool.com. She is a “self-proclaimed scripting junkie”, therefore I bookmarked her blog immediately. Scripts are really usefull, and sharing them is shockingly simple. It is also a really rewarding way of helping other people, all free of charge. So kudos for Michelle. I will post a copy of the script she created here for your (and mine) reference, but be sure to go to her blog and search for the current version should you need this (type “Index Defrag Script” in the search box). I will post a customized version of it here, as soon as I do some testing on it. While this is at works, please go to her blog and check out the current version of Index Defrag Script and much more great scripts!