geowilli
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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'
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'
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?
ASKER