Post reply

Indexes

  • July 4, 2003 at 2:02 am

    #64785

    One of our nice developers has managed to create over 300 indexes with a fill factor of 90 (good eh) on a db that is about 15Gb in size. I would like to drop all the indexes and recreate them with a fill factor of 10. Can anyone give me a hand in scripting this.

  • July 4, 2003 at 2:10 am

    #464165

    Answered my own question.

    Here it is

    This query creates a script which you then need to run the results of

    select 'drop index ' + object_name(id) + '.' + [name] from sysindexes

    where OrigFillFactor = 90

    and NOT (([name] like '_W%') or ([name] like 'A_%'))

  • July 4, 2003 at 2:13 am

    #464166

    Atleast SQL Optimiser won't have to look long and hard for an index.

    Sorry, had to throw in my ten cents worth...

    Cheers,

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • July 7, 2003 at 6:26 am

    #464274

    I think dbcc dbreindex with the fillfactor option could do well here too (except you would have to run dbcc for each table in your database).


    Joseph

  • July 9, 2003 at 1:02 pm

    #464726

    ...sp_MSforeachdb

    ...sp_MSforeachtable

    /Hans

  • July 10, 2003 at 11:43 am

    #464947

    quote:

    One of our nice developers has managed to create over 300 indexes with a fill factor of 90 (good eh) on a db that is about 15Gb in size. I would like to drop all the indexes and recreate them with a fill factor of 10. Can anyone give me a hand in scripting this.

    So what fill factor do you want, and why? Just trying to get a better understanding.

    Thanks,

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply