Good for: rebuilding indexes without using DBCC DBREINDEX; avoiding a bug (SP3) in which DBCC “forgets” OrigFillFactor and rebuilds huge, bloated indexes.
What it does: for a given table, the proc finds all the (nonclustered) indexes, generates the appropriate statements to drop and rebuild the indexes (i.e., DROP/CREATE INDEX or ALTER TABLE DROP/ADD CONSTRAINT), and then executes the statements.
Notes: 1) the proc does not look at nonclustered indexes, nor does it take into account foreign-key relationships. You will still need to drop any foreign-key relationship before rebuilding an index that is part of a foreign-key relationship. Someday I will get around to adding code to handle these cases automatically <g> 2) I have commented out the actual EXEC statements and have added code to PRINT the DROP/CREATE statements so you can have a look at them before turning this thing loose on your database; when you are ready, just uncomment the EXEC @DropStatement and EXEC @CreateStatement lines.
In my (production) version of this proc I have also added some code that looks at the ratio of index size to data size, and then reindexes (or not) based on that result. Since I am using this proc to manage indexes on about 60 remote boxes, this “set it and forget it” feature works out very well for me. Since this decision-making may not be useful for everyone I have not included that code in this version. If you would like to see that version, or if you have any suggestions, please write me.
For SQL Server 6.5
Author: Karen Collins
Download Script:
utl_rebuildidxes.sql
Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…
Disclaimer Continued
Back to Database Journal Home
