Forums Neueste Beiträge
 

is index existing ?

07/03/2008 - 12:16 von Eckhard Sallermann | Report spam
hi

will delete an index of a table ( drop index postbest.IX_postbest_bestnr )
but only, if that index already exists
is it possible to determine, if that index already exists in the table ?

is it possible to delete all indexes for a table in one step ?
 

Lesen sie die antworten

#1 Torsten Schuessler
07/03/2008 - 13:07 | Warnen spam
Hi Eckard,

something like this - for a table and all indexes:

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

... for only one index with checking:

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id OBJECT_ID(N'[dbo].[postbest) AND name = N'IX_postbest_bestnr ')
DROP INDEX [IX_postbest_bestnr ] ON [dbo].[postbest]

CU
tosc

InsideSQL.org: http://www.insidesql.org
Blog: http://www.insidesql.de/blogs/tosc

"Eckhard Sallermann" schrieb im Newsbeitrag
news:fqr86p$5sr$
hi

will delete an index of a table ( drop index postbest.IX_postbest_bestnr )
but only, if that index already exists
is it possible to determine, if that index already exists in the table ?

is it possible to delete all indexes for a table in one step ?







Ähnliche fragen