Alter Field in Table - 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 Field in Table

Author

Topic

yenny.lauw
Starting Member

14 Posts

Posted - 2006-09-20 : 03:20:27

Hi guys,

I'm trying to alter my several of data types field in several table. I already create the script, it works well, but the error raise, if the field that i'm trying to alter is used as index on its table.

How do I solve this, since I don't want to remove the table's index?

Thanks a lot

Cowboy | Alopecia Areata

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-20 : 03:34:00

1. remove the index
2. alter the field
3. re-create the index

yenny.lauw
Starting Member

14 Posts

Posted - 2006-09-20 : 03:36:54

so i have to keep the record of the index ?
phew ... where do I start ?
What system table do I need to check in order to get the index field list from a table ?

Cowboy | Alopecia Areata

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-20 : 03:43:50

You can also do the alternation via Enterprise Manager table design. There is a button to save the changes script to file.

yenny.lauw
Starting Member

14 Posts

Posted - 2006-09-20 : 03:50:15

hi khtan, will you please be kind tell me code how to detect wheater this field is in index or not ? then using what system SP to drop the index and create it again.

Thx, sorry if my question is very basic

Cowboy | Alopecia Areata

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-20 : 04:13:10

this will lists the index and the column name for a table


select 	name as index_name, indid, n, index_col('ordh', indid, n) as column_name
from 	sysindexes i cross join
	(
		select	1 as n union all select 2 as n union all select 3 union all select 4
	) n
where 	id = object_id('table') 
and 	indid > 0 and indid < 255 and (status & 64) = 0 
and	index_col('table', indid, n)	is not null
order by indid

yenny.lauw
Starting Member

14 Posts

Posted - 2006-09-24 : 23:09:55

hi khtan, thx for the code.

but if I drop the index, wouldn't that mean i also lose the information about all field for that index ?
does this mean I have to create temprorary table that save detail of index i'm about to drop ?

Cowboy | Alopecia Areata

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources