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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL Scripts & Samples

Posted Sep 14, 1998


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:

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

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