Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Sep 13, 1998

utl_rebuildidxes.sql

By DatabaseJournal.com Staff



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



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date