Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Location
    South Africa
    Posts
    1

    Unanswered: Drop All database indexes

    How can I drop all the indexes in all the tables in a MS 2000 SQL Server?

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Drop All database indexes

    select name from the sysindexes table for all user tables, concatenate with 'drop index '. and execute it. Not very simple yet dangerous too.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Drop All database indexes

    Originally posted by freddyb
    How can I drop all the indexes in all the tables in a MS 2000 SQL Server?
    select case when sop.xtype in('PK','UQ')
    then
    'alter table '+so.name+' drop constraint '+sy.name+char(13)+'go'
    else
    'drop index '+so.name+'.'+sy.name+char(13)+'go'
    end
    from sysindexes sy
    join sysobjects so on so.id=sy.id and so.xtype='U'
    left join sysobjects sop on sop.parent_obj=so.id and sop.name=sy.name
    where sy.indid not in(0,255)

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    this isnt another I want to do it and DO IT NOW! deal is it ?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: Drop All database indexes

    Originally posted by freddyb
    How can I drop all the indexes in all the tables in a MS 2000 SQL Server?
    I'm just being curious, but why on earth would you want to? If you want to shoot yourself in the foot, go outside and get it over with quickly... Dropping all the incidies will get you to the same point, but it seems like it would be a lot more agonizing in the process.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    pat
    you know what a really cool way to do this is
    when you generate a database script, justs choose indexes and indicate that you want the script to contain the tests for existence and the drop clauses.

    then on the last page of the dialog tell it to create 1 script for each object
    imagine 100+ indexes

    Yee Haw

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Ruprect
    pat
    you know what a really cool way to do this is
    when you generate a database script, justs choose indexes and indicate that you want the script to contain the tests for existence and the drop clauses.

    then on the last page of the dialog tell it to create 1 script for each object
    imagine 100+ indexes

    Yee Haw
    I must have "missed a meeting" somewhere. This is a good thing???

    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think freddyb is somehow related to the other guy who was trying to "DROP ALL TABLES IN ONE SHOT".

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Hello! McFLy.. Pay Attention, McFly!

    this isnt another I want to do it and DO IT NOW! deal is it ?
    McFLy, You Irish Bug!

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Scott does...DTS

Posting Permissions

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