How can I drop all indexes from a table, if I do not know exactly which
indexes exist?

Can I use the sysindexes to generate a stored procedure or something like
that?

Jan van Veldhuizen wrote:
You can certainly create a stored procedure to drop indexes. However, a
generic procedure seems not possible as - AFAIK - you can't use variables to
specify the table owner.

We have - for a similar purpose - created a tool which runs as a Windows
executable & lets you use internal variables i.e. to specify the owner of a
table. This would be a valid script for our tool:

create table TEST (ID INTEGER);
create index TEST_IND on TEST(ID);
select * from dbo.sysindexes;
%% read the name of the index into a local variable
select INAME from sys.sysindexes where tname = 'TEST';
%% use local variable to specify the index name
drop index $INAME;

Klaus

--
Die email Adresse (reply-to) im header ist ungueltig.
Fuer mail "pub . kp2 . pieper @ ibeq . com" benutzen.
(Leerzeichen loeschen).

Reply-to invalid.
Use "pub . kp2 . pieper @ ibeq . com" (remove spaces).

Thx.
Meanwhile I found a solution:

select 'drop index ' + iname + ';' from sysindexes where tname =
'temptable'; output to 'c:\dropall.sql' quote '';
read c:\dropall.sql
go
commit work
go





"Klaus P. Pieper" <pub.kp2.pieper (AT) ibeq (DOT) com> wrote

Use EXECUTE IMMEDIATE and a cursor loop if you want to avoid the
intermediate file.

Breck

On 8 Dec 2003 05:47:23 -0800, "Jan van Veldhuizen"
<jan (AT) van-veldhuizen (DOT) nl> wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com