I am about to write a script to add new indexes to a database, I wish that script to work regardless of the current indexes the database has.
Therefore th开发者_开发知识库e first step in the script should be to remove all the current indexes. (I could just as easily run .net code, but I think TSQL will be less painful for this type of thing)
(This is for use in development for small databases, so I don’t mind if it is not the quickest way of managing indexes, this needs to work for SqlServer 2005 and SqlServer 2008)
I found this at http://refactormycode.com/codes/522-drop-all-indexes-in-sql-server. It appears to drop all indexes in the current database:
Declare @Index varchar(128)
Declare @Table varchar(128)
Select
SysIndexes.Name As 'Index',
SysObjects.Name As 'Table'
Into
#Indexes
From
SysIndexes
Inner Join SysObjects On
SysObjects.id = SysIndexes.id
Where
SysIndexes.Name Is Not Null
and SysObjects.XType = 'U'
Order By
SysIndexes.Name,
SysObjects.Name
While (Select Count(*) From #Indexes) > 0
Begin
Set @Index = (Select Top 1 [Index] From #Indexes)
Set @Table = (Select Top 1 [Table] From #Indexes)
--Print 'Drop Index [' + @Index + '] On [' + @Table + ']' + Char(13)
Exec ('Drop Index [' + @Index + '] On [' + @Table + ']')
Delete From #Indexes Where [Index] = @Index and [Table] = @Table
End
Drop Table #Indexes
Another working script:
http://blogs.msdn.com/b/tihot/archive/2006/01/16/513548.aspx
DECLARE @indexName NVARCHAR(128)
DECLARE @dropIndexSql NVARCHAR(4000)
DECLARE tableIndexes CURSOR FOR
SELECT name FROM sysindexes
WHERE id = OBJECT_ID(N'tableName') AND
indid > 0 AND indid < 255 AND
INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY indid DESC
OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName
WHILE @@fetch_status = 0
BEGIN
SET @dropIndexSql = N'DROP INDEX tableName.' + @indexName
EXEC sp_executesql @dropIndexSql
FETCH NEXT FROM tableIndexes INTO @indexName
END
CLOSE tableIndexes
DEALLOCATE tableIndexes
精彩评论