Create Index not working with Drop Index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Create Index not working with Drop Index

Author

Topic

mpalam
Starting Member

4 Posts

Posted - 2006-11-24 : 06:47:03

I want to drop the existing index if it exists and then recreate it or create a new index if it doesnt exist using code:

create index WP_INDEX on dbo.WP_TABLE(ACT_ID, ACT_DB) WITH DROP_EXISTING

But it is giving error that there is no index by that name in the database.
Its not creating a new index if it doesnt exist. Can anyone provide the correct query?

Thanks,
mpalam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 06:53:42

Something like this

if exists (select * from dbo.sysobjects where id = object_id(N'WP_INDEX'))
	ALTER TABLE dbo.WP_TABLE DROP CONSTRAINT WP_INDEX

create index WP_INDEX on dbo.WP_TABLE(ACT_ID, ACT_DB)

Peter Larsson
Helsingborg, Sweden

mpalam
Starting Member

4 Posts

Posted - 2006-11-24 : 06:59:24

Hi Peter Larsson

As I am not familiar much with the SQL Server can you please explain what this code line means:
(select * from dbo.sysobjects where id = object_id(N'WP_INDEX'))

and what is (N'WP_INDEX')?

Thanks
mpalam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 07:04:08

Peter,

Why Alter Table Drop Constraint? Why not Drop Index?

IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'WP_INDEX')
   DROP INDEX WP_TABLE.WP_INDEX
GO

create index WP_INDEX on dbo.WP_TABLE(ACT_ID, ACT_DB)
GO

Harsh Athalye
India.
"Nothing is Impossible"

mpalam
Starting Member

4 Posts

Posted - 2006-11-24 : 07:06:13

Thanks Harsha

That's easier for me to visualize.
Will try this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 07:31:46

I just took an example from Books Online and edited the names...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources