Posted by rcamarda on 10/06/06 21:55

Hi,
I found this SQL in the news group to drop indexs in a table. I need a
script that will drop all indexes in all user tables of a given
database:

DECLARE @indexName NVARCHAR(128)
DECLARE @dropIndexSql NVARCHAR(4000)

DECLARE tableIndexes CURSOR FOR
SELECT name FROM sysindexes
WHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')
AND indid > 0
AND indid < 255
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName
WHILE @@fetch_status = 0
BEGIN
SET @dropIndexSql = N' DROP INDEX
F_BI_Registration_Tracking_Summary.' + @indexName
EXEC sp_executesql @dropIndexSql
FETCH NEXT FROM tableIndexes INTO @indexName
END
CLOSE tableIndexes
DEALLOCATE tableIndexes
TIA
Rob

• Next in forum: Delay between CREATE DATABASE and ability to connect to that database
• Prev in forum: Re: Replacing the second carriage return
• Thread view: Drop all indexes in a table, how to drop all for user tables in database

[Reply to this message]