Post reply

Need help scripting the Create/Drop index statement for all indexes within a database

  • June 6, 2005 at 2:40 am

    #86898

    I tried using the ent mgr script tool, but it appears to drop/create the tables as well.  this made me a little nervous.

  • June 6, 2005 at 5:17 am

    #563156

    If you just want the indexes, PKs, etc...  Just uncheck the DROP\CREATE tables check boxes



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • June 6, 2005 at 6:04 am

    #563163

    Where are those check boxes? I can only find CREAT / DROP < object >

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 6, 2005 at 9:39 am

    #563267

    Thanks for the help. I was able to script the create index event. Now, Do i need to add a line to drop each index if it exist?

  • June 7, 2005 at 6:59 am

    #563481

    It's always a good idea to have a check before a drop. That way you won't have errors when you run the script.

  • June 7, 2005 at 4:11 pm

    #563704

    I created a script that loops thru user tables checking for index existing on table.

    if index(s) found - (as Remi noted ), it runs a reindex routine, otherwise, skips table.

    partial snippet rebuild with 70 fill factor:

    BEGIN

    PRINT 'Executing DBCC db reINDEX (' + RTRIM(@tblname)+ ', '''', 70)'

    SELECT @execstr = 'DBCC DBREINDEX (' + RTRIM(@tblname) + ', '''', 70)'

    EXEC (@execstr)

    FETCH NEXT

    My understanding of DBCC DBREINDEX on a table name is where it will drop and recreate indexes. more parameters, more control.

    This level and detail was fine for me, and allos the drop and create of all user table indexes in my database(s). ( as far as I know...)

    While BOL says to abandon DBREINDEX for DROP and CREATE... it also states that DBREINDEX is automatically atomic, where DROP and CREATE need transaction wrappers to achieve the same... ( more work...)

    enjoy

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

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