• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Need to drop all indexes in SQL 6.5

I am upgrading from SQL Server 6.5 to SQL Server 2000.  I would like to drop all the indexes in the SQL 6.5 database before doing the upgrade.  Is there any way to script this or do it painlessly?
0
geowilli
Asked:
geowilli
  • 2
  • 2
1 Solution
 
spcmnspffCommented:
Here's one I use regularly . . .

CREATE Procedure spDropIndexes
AS

Declare @IndexName VarChar(100)

Declare Indexes CURSOR FOR
SELECT 'dbo.' + '[' + sysobjects.name + '].[' + sysindexes.name +']'
FROM sysindexes INNER JOIN sysobjects
    ON sysindexes.id = sysobjects.id
    Inner Join tblTables On sysobjects.name = tblTables.TableName
WHERE IndID Not In(0,255) And sysindexes.[Name] Not lIke '_WA_SYS%'

Open Indexes

Fetch Next From Indexes Into @IndexName

While @@Fetch_Status = 0
Begin
     Exec('Drop Index ' + @IndexName)
     Fetch Next From Indexes Into @IndexName
End

Deallocate Indexes
0
 
geowilliAuthor Commented:
I tried the code but it could not find the table 'tblTables'
0
 
geowilliAuthor Commented:
thanks spcmnspff

I got the code to work by making the following alteration to the select statement

SELECT 'dbo.' + '[' + sysobjects.name + '].[' + sysindexes.name +']'
FROM sysindexes INNER JOIN sysobjects
   ON sysindexes.id = sysobjects.id
WHERE IndID Not In(0,255) And sysindexes.Name Not lIke '_WA_SYS%'
     and sysobjects.Type = 'U'
0
 
spcmnspffCommented:
Yeah sorry about that, I diidn't edit that query before I put it in there.  In my case I have a table that contains a list of potential tables that I want to drop the indexes on.  If you remove the join to that table you should be fine.  One additional comment regarding this code the not like '_WA_SYS%' refers to an undocumented SQL server feature.  Records in Sysindexes that match this criteria are faux indexes that the optimizer has created to indicate where a potential index is needed.  It means that you have queried this object and a table scan had resulted.  When you use the index tuning wizard it simply does a select in sysindexes for these records and makes the apropriate suggestion.... cool huh?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now