Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • 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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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