Drop all indexes in a table, how to drop all for user tables in database

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

Quote:

Originally Posted by

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|||Thanks Damien,
You are correct. I am using Cognos' Data Manager to build my data
warehouse. In on database, I truncate and reload all the data via it's
SQL API, so I loads pretty quick. It maintains the indexes, however I
indexes I build outside the tool. Since I truncate the tables, I dont
want indexes updated during the load, I will build them after. Hence,
the need to drop all indexes of my table before I load the data.
TYVM!
rOB

Quote:

Originally Posted by

>
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