Deleting Indexes

20 Oct 2005 2:59 PM

JD

Is there a way to delete the indexes on all the tables for a specific
database in SQL Server using Query Analyzer without knowing the exact name of
the Index? I am able to do this using SQLDMO, but I need to know if there is
a way to do this using Query Analyzer.

Thanks

20 Oct 2005 3:33 PM

Ed

You might want to look at the sysindexes table

Select * from sysindexes

I would create a cursor to loop through the sysindexes table and issue the
command
Drop Index TableName.IndexName

Both TableName and IndexName can be found in Sysindexes table

HTH

Ed

Show quote

"JD" wrote:

> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name of
> the Index? I am able to do this using SQLDMO, but I need to know if there is
> a way to do this using Query Analyzer.
>
> Thanks

20 Oct 2005 3:37 PM

Aaron Bertrand [SQL Server MVP]

This will not take care of indexes implicitly created by, say PK and Unique
constraints, but it may be a start:

select 'DROP INDEX '+object_name(id)+'.'+object_name(object_id(name))
from sysindexes
WHERE objectproperty(object_id(name), 'IsMsShipped') = 0
AND objectproperty(id, 'IsMsShipped') = 0
AND keycnt = 0

Show quote

"JD" <J*@discussions.microsoft.com> wrote in message
news:C554DC0B-22B6-4763-8A10-052843C5D47C@microsoft.com...
> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name
> of
> the Index? I am able to do this using SQLDMO, but I need to know if there
> is
> a way to do this using Query Analyzer.
>
> Thanks

20 Oct 2005 3:50 PM

Enric

hi,
if you know the name of the table using sp_help <table> you obtain the name
of its indexes.
regards,

Show quote

"JD" wrote:

> Is there a way to delete the indexes on all the tables for a specific
> database in SQL Server using Query Analyzer without knowing the exact name of
> the Index? I am able to do this using SQLDMO, but I need to know if there is
> a way to do this using Query Analyzer.
>
> Thanks