Drop all indexs with a stored procedure

I want to call a script/sproc at the beginning of a nightly ETL process that
will drop all indexes in a user-defined database.
At the end of the ETL process I will call a stored procedure (I already
have) to recreate the indexes.here's an undoc trick:
exec master..xp_execresultset N'select
''drop index ''+quotename(o.name)+''.''+quo_tename(i.name)
from sysindexes i join sysobjects o on i.id=o.id
where o.type=''U''
and i.indid > 0 and i.indid < 255
and (i.status & (32|64|2048|4096))=0
',N'Your_db_name_goes_here'
For more info on this trick:
http://rac4sql.net/xp_execresu_ltset.asp
-oj
"LP" <LP@.discussions.microsoft.com> wrote in message
news:FCD018BC-D7E2-446B-A49B-CF99FC3F160B@.microsoft.com...
>I want to call a script/sproc at the beginning of a nightly ETL process
>that
> will drop all indexes in a user-defined database.
> At the end of the ETL process I will call a stored procedure (I already
> have) to recreate the indexes.
>

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