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.
| Author |
Topic |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2005-08-24 : 20:21:00
|
| Hi FriendsI want to change datatype of a column from text to varchar.I know i can do one at a time.but there r too many to do.is there any quicker way changing datatypes in a specified table.ThanksCheers |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-08-24 : 20:35:31
|
| Easiest way (for me), In EM, right-click on the table, select "Design Table". The GUI will let you change data types on the fly.Sam |
 |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-08-24 : 20:38:40
|
| thats correct Sam.if i had to change 40 cols in a single then its little pain,isnot it.i thought we could do it thru some scriptCheers |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-08-24 : 20:44:31
|
| hi rajani unfortunately it can be complex- in the general case, if the field is indexed, triggered or part of a relationship, then these may have to be dropped and recreated, however, for a text field, I'll assume none of these are the case.If you need to do it en-masse, then you'll also need to define what happens to extra data (in case any of your text fields are large) - do you just want to clip any extra characters off?One way would be to write a script which loops through all the tables which have the text field, then loops through all the text fields you want to change, * add a new column which is varchar* copy the data from the text to a varchar (update query)* drop the old data (ie the text column)* change the name of the varchar column back to the correct nameI just had to do exactly this on an access database...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-08-24 : 21:40:18
|
| >>unfortunately it can be complex- in the general case, if the field is indexed, triggered or part of a relationship, then these may have to be dropped and recreated, however, for a text field, I'll assume none of these are the case.Yes,thats true.i dont worry abt those.>>If you need to do it en-masse, then you'll also need to define what happens to extra data (in case any of your text fields are large) - do you just want to clip any extra characters off?i know most of those cols wont have data more than a certain length and would not worry even if data is truncated.yup,looks like i need to write that script to do what i want.Cheers |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-08-24 : 21:49:05
|
| Hope it goes well - let us know if you need help - I just thought I should add, that you could use the sysyobjects/syscolumns information to work out which columns are "text" if you don't want to have to specify them all...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-08-24 : 22:38:50
|
| Thanks mate.Cheers |
|
|
|
|
|
|
|
|
changing datatypes