Check for existence of an Index 1

[ks1392]

I'm sure this is an easy one, but is there a way I can check for the existence of an index on a specific table, and if it exists to drop it using T-SQL?

TIA - Kerr

 

[osjohnm]

Hi

Run the following on the table:

exec sp_helpindex 'tablename'

that will return all the indexes for the specified table.

you can also run a select statement on the sysindexes system table in each database which will return all the indexes in every table in the particular database.

for dropping the index use this in the database once you have found the index name:

DROP INDEX tablename.indexname

Hope this helps

John

 

[JohnDTampaBay]

What version of SQL are you running? There are a lot of meta data functions available in SQL 2000 to used to access information like this. Stick with those functions, if possible. Microsoft warns against querying the system tables directly and they reserve the right to change the contents and structures without notice. Having said that, you can query the SysIndexes and SysObjects tables to determine if a user table has a specific index in SQL 6.5 and later (I wasn't around for earlier versions).
--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
(0 row(s) affected)

 

[ks1392]

Okay, two ways of going about it - good! Thanks for your help folks!

 

[tlbroadbent]

Use the following syntax to check for a specific index and drop it if it exists.

If Exists
(Select *
From sysindexes i
Join sysobjects o
On i.id=o.id
Where o.name='table_name' And i.name='index_name')

Drop Index table_name.index_name

Use the following script to drop all indexes on a table.

declare @sql varchar(8000)
set @sql=''

Select @sql=@sql+
Case When i.Status & 2048 = 2048
Then 'Alter Table ' + o.name + ' Drop Constraint ' + i.name
Else 'Drop Index ' + o.name + '.'+i.name End + char(10)
From sysindexes i
Join sysobjects o
On i.id=o.id
Where o.name='ebase'
And i.indid>0
And i.indid<255
And left(i.name,4)<>'_WA_'

exec(@sql) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[ks1392]

Even better - this is what I was looking for - an if statement to check for existence before dropping - Thanks!