Seeking script to drop/create all indexes in database
-
I don't know if one exists however I'm curious and thought I would ask, does anyone know of an exisiting sql script that could drop and recreate all exisiting indexes for a database?
Appreciate replies
-
I believe there are a number existing on this site; however, you can start having a look at this one:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=451
-
Not sure why you'd want to do that when a good maintenance plan will achieve the same result of having properly ordered and defragmented indexes. So, I have to ask, why would someone want to do this? Not trying to be a smart guy here... I really want to know
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Why don't you use the "Generate Script" feature in EM to create a script that drops/creates all indexes?
Have a good day,Norene Malaney
-
Ok, Jeff, I'll tell you
Ever fumbled around with index fill factors? There are times when they have to be adjusted. How should a maintenance plan tell by itself, what fill factor to apply? There are some very good articles about this in the articles section.
_/_/_/ paramind _/_/_/ -
If a table exists, then you can't drop the index.
if that particular table is deleted then its index will be deleted.
Regards
Amit Gupta
-
plain wrong.
_/_/_/ paramind _/_/_/ -
Depending upon your reason for wanting to drop and re-create indexes, dbcc dbreindex may be something you wish to explore. It can rebuild all the indexes for a table without knowing the names of the indexes.
Steve
-
A few years ago, I had a requirement to drop indexes while a full refresh of data went on for importing purposes. I had to write some scripts to store all the indexes and thier rebuilds in a table, drop the indexes, perform the inserts, and rebuild the indexes. The bottom line to this is that I believe I still have the code for this laying around somewhere, and if you still need this, I would be happy to post it or send it to you.......let me know....
-
Yep, I agree...
if you need to fumble with fill factors for all tables, then you are correct... hopefully, though, you let the default fill factor of 90% be applied to most of your indexes and only tuned the ones that need it... like assigning 100% to truly static lookup tables or 80% (or less depending) to high-transaction tables. After that, why would you need to adjust every index with a different fill factor?And, of course, a maintenance plan isn't going to change fill factors for you.
Anyway, you mention that there are some very good articles about this in the articles section... there's nothing for the search of "fill factor" and theres a million
about indexing... did you have a particular couple of articles that you might list the link for?--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply