Re: indexes and sysindexes

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 05/07/04 

Date: Fri, 7 May 2004 14:33:56 +0200

The join to sysobjects is redundant. As for the query, you need to filter statistics, OK and UQ indexes etc
out. Use the INDEXPROPERTY function for this. It is documented in Books Online. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Repstat" <anonymous@discussions.microsoft.com> wrote in message
news:4105A138-7F3C-4CD6-BA81-13B502D04CB9@microsoft.com...
> I am looking for a way to write a procedure to drop all indexes from a table, however many it may have, by
creating a procedure that takes the table's name as a parameter, I have tried enumerating the sysindexes table
but to no avail, as some of the rows in there don't relate to actual indexes. The purpose of this being to
clear all indexes in simple reusable code, in order to do a fast insert on the table.
> Here is the code I currently have:
>
> create proc clearindexes
> @tablename sysname
> as
>
> declare @indexname sysname
> declare @sql varchar(2000)
> declare repstat cursor
> local
> fast_forward
> read_only
> for select i.name
> from sysindexes i
> join sysobjects o on (i.id = o.id)
> where o.id = object_id(@tablename)
> open repstat
> fetch repstat into @indexname
> while @@fetch_status =0
> begin
> select @sql = 'drop index ' + @tablename + '.' + @indexname
> execute(@sql)
> fetch repstat into @indexname
> end
> close repstat
> deallocate repstat
>
> But like I say it's not working because there are extra rows in the sysindexes table. Can anybody tell me
how to identify these extra rows with a where clause maybe?
>
> Thanks!
>

Relevant Pages

  • Re: indexes and sysindexes
    ... I have tried enumerating the sysindexes table ... > declare repstat cursor ... > fetch repstat into @indexname ... > But like I say it's not working because there are extra rows in the ...
    (microsoft.public.sqlserver.programming)
  • indexes and sysindexes
    ... declare @indexname sysname ... declare repstat cursor ...
    (microsoft.public.sqlserver.programming)