alter index in sql server2000 - SQL Server Forums

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

Transact-SQL (2000)

alter index in sql server2000

Author

Topic

yenny.lauw
Starting Member

14 Posts

Posted - 2006-10-04 : 05:21:06

hi guys is it possible to alter index in sql2000 ?

I have tables which consist of several index,
one of the index consist of field that I need to alter,
since this field is indexed, I can't do that, I have to drop the index then rebuild it.

Eg :
Index : IX_Table1
Fields : Field1
Field2
Field3

Before I can successfully alter field2 datatype, I have to drop the index, then rebuild it along with field1, field2, field3.

Is there any easy way for me to do this ?

thx before

Cowboy | Alopecia Areata | Fold T-Shirt Fast | Naked in 7 seconds

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-04 : 05:26:21

>> Is there any easy way for me to do this ?
Not sure what your problem is.

Have a look at drop index and create index in bol.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yenny.lauw
Starting Member

14 Posts

Posted - 2006-10-04 : 05:29:14

i know about drop and recreate, but i'm a little bit confused about the syntaks for recreating my index.
I read some of the website about temporary disable index, but it is only for sql server 2005

Cowboy | Alopecia Areata | Fold T-Shirt Fast | Naked in 7 seconds

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-04 : 05:40:03

drop index <tblname>.<indexname>

create index <indexname> on <tablename>
(col1, col2, col3)
create unique index <indexname> on <tablename>
(col1, col2, col3)
create unique clustered index <indexname> on <tablename>
(col1, col2, col3)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yenny.lauw
Starting Member

14 Posts

Posted - 2006-10-05 : 00:02:00

hi nr, thx, it works....
but there is something weird going on, after alter several table (drop index also), I can't drop user data type because it's still used ... when I check the sysobjects, there is one record that still have 2 fields reference to user data type that i'm about to drop, but the xtype of this record is "IF", while i only check for "U" (Table), "P" (SP), "FN" (Function), and "V" (View). What is xtype "IF" anyway ?

Cowboy | Alopecia Areata | Fold T-Shirt Fast | Naked in 7 seconds

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 00:12:00

from BOL

quote:

Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

yenny.lauw
Starting Member

14 Posts

Posted - 2006-10-05 : 02:34:58

hi khtan, thx for da info, I did search BOL, but couldn't find this, perhaps the keyword isn't right

Cowboy | Alopecia Areata | Fold T-Shirt Fast | Naked in 7 seconds

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-05 : 02:39:46

quote:
Originally posted by yenny.lauw

hi khtan, thx for da info, I did search BOL, but couldn't find this, perhaps the keyword isn't right

Cowboy | Alopecia Areata | Fold T-Shirt Fast | Naked in 7 seconds

it's under sysobjects

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources