Posted by Damien on 10/09/06 07:31

rcamarda wrote:
> 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

Hi Rob,

DECLARE @indexName sysname --Changed to sysname, since that's what it
was
DECLARE @tableName sysname
DECLARE @dropIndexSql NVARCHAR(4000)

DECLARE tableIndexes CURSOR FOR
SELECT name,OBJECT_NAME(ID) FROM sysindexes
WHERE OBJECTPROPERTY(ID,N'IsTable') = 1 AND
OBJECTPROPERTY(ID,N'IsMSShipped') = 0
AND indid > 0 --Is this right? Wouldn't this attempt to drop
clustered PK?
AND indid < 255
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName, @tableName
WHILE @@fetch_status = 0
BEGIN
SET @dropIndexSql = N' DROP INDEX ' + @tableName + '.'
+ @indexName
EXEC sp_executesql @dropIndexSql
FETCH NEXT FROM tableIndexes INTO @indexName, @tableName
END
CLOSE tableIndexes
DEALLOCATE tableIndexes

I assume you're doing this for some kind of maintenance procedure in
your database, where you've already scripted off all of the indexes.

Damien

• Next in forum: Re: Drop all indexes in a table, how to drop all for user tables in database
• Prev in forum: Re: how to delete lines from text file
• Thread view: Re: Drop all indexes in a table, how to drop all for user tables in database

[Reply to this message]