EDIT: There is a new version of this stored procedure.
I've made a couple of changes to my defragment indexes stored procedure, isp_ALTER_INDEX, based upon feedback I've received from my blog readers. For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command. Check the comment header block for its documentation.
Here are the changes:
- Added support for database and object names with special characters.
- Added support for indexes with the ALLOW_PAGE_LOCKS option enabled. Online reindexing is not available for indexes with this option enabled.
- Fixed the code so that it handles "gaps" in the temp table. In previous versions, you would hit a gap in the identity values of the temp table if rows had been deleted from it due to @minRowCount. This issue caused all indexes after the gap to not get defragmented.
In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.
Let me know if you'd like me to add any other features or if you find a bug.
You can download the stored procedure here.