Results 1 to 5 of 5

Thread: Drop indexes

  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Angry Unanswered: Drop indexes

    I have a database with many PK and FK constraints. Is there any script I can use to drop all indexes and rebuild them taking care of PK and dependencies? I am unable to drop them by tablename order. This is on MSSQLServer-2000 SP3. Any help is appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    why drop and recreate when you can defrag or reindex? have you looked at DBCC SHOWCONTIG to make you sure need to do this.

    btw, you only have to do the clustered ones. the nonclustered get rebuilt when you do this.

    here is some code...

    SELECT 'DBCC DBREINDEX(' + CAST(o.[name] as varchar(200)) + ',' + CAST(i.[name] as varchar) + ')
    GO'
    FROM sysindexes i
    JOIN sysobjects o
    ON i.id = o.id
    WHERE o.xtype = 'U'
    AND i.indid = 1
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Thanks but what I also need

    is I have primary clustered indexes on 812 tables and do not know a lot of dependencies if I go by tablename order to drop and recreate indexes. Does DBCC REINDEX drops and recreates PK indexes too? We have run DBCC showcontig and saw fragmentation quite a bit.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    DBCC DBREINDEX does not drop and recreate to knowledge but I would confirm in BOL. It does however lock up tables.

    DBCC INDEXDEFRAG is slower but does not lock up the tables so much and the users can work more easily while this goes on.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    DBCC DBREINDEX does not drop and recreate to knowledge but I would confirm in BOL.
    It doesn't - in fact, when last I read about all this stuff it was presented as DBREINDEX's USP
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •