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 |
|
karan@talash.net
Starting Member
12 Posts |
 Posted - 2002-07-01 : 02:13:19
|
| Hi Viewer,I want to change the datatype of a particular field in a table in a remote database by writing a script.How shall i do it?Regards,Karan |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-07-01 : 02:18:25
|
| You can'tYou have to copy all your data into a holding table. Then drop the original table (and any constraints associated). Then recreate the table with the correct data types. Then copy your data back in and recreate your constraints.This is what happens behind the scenes in Enterprise Manager when you do it there.Damian |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-07-01 : 14:44:03
|
| There is a very limited set of conditions where you can just issue an ALTER TABLE...ALTER COLUMN... statement to change the datatype (at least it works in SQL 2000). At the least, the datatypes that you are changing to and from have to be implicitly convertible and no data in the column will be out of range. Also, if you have any foreign keys or indices referencing this column, you'll be in trouble. Overall, Merkin's solution is the most complete and works in all circumstances (you'll have to drop / recreate any indices and foreign keys). |
|
|
|
|
|
|
Changing of datatype of a field in a table