Dropping an Index

• Bill Cockerill

  SSC Enthusiast

  Points: 195

  More actions

  March 21, 2005 at 3:45 am

  #67173

  Dear Forum,

  I want to do the equivalent of:

  If Object_ID('Table1') is not NULL Drop Table Table1;

  for indices.

  I have tried:

  If Object_ID('Table1.Table1_Index_1') is not NULL Drop Index Table1.Table1_Index_1;

  And

  If Object_ID('Table1_Index_1') is not NULL Drop Index Table1.Table1_Index_1;

  and neither works, although the simple drop does work providing the index exists.

  Any ideas what I'm doing wrong?

  Thanks, Bill

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 21, 2005 at 4:19 am

  #547967

  A dirty workaround might be:

  USE PUBS

  IF EXISTS(SELECT * FROM sysindexes WHERE [name] = 'PK_emp_id')

   Do something...

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  March 21, 2005 at 6:42 am

  #547986

  You could also use this is the index is not the primary key :

  Select * from dbo.SysIndexes where id = object_id('TableName') and name = 'IndexName'

• Bill Cockerill

  SSC Enthusiast

  Points: 195

  More actions

  March 21, 2005 at 7:20 am

  #547995

   Thanks guys this method does the job. I don't mind dirty!

  Cheers, Bill