By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,584 Members | 1,750 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,584 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Oct 6 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Oct 9 '06 #2

P: n/a
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
>
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
Oct 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.