May 31, 2002 at 9:13 am
Hi Everyone:
Can anyone please tell me how I can modify the column (varchar) width from 255 to say 2000 by using T_SQL....what is the syntax? I know I can get it done through EM.But I need to know how to do it by using T_SQL...Thanks
May 31, 2002 at 9:17 am
If you do it in EM, it will generate a script for you. You have to drop the table and rebuild, but you can rename the table as a temp so you do not lose data.
Steve Jones
May 31, 2002 at 10:51 am
You can try :
update syscolumns
set length =2000
where id =object_id('your_table')
and name = 'column name'
The problem is - you can get an error message :
"Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this."
May 31, 2002 at 10:52 am
EM reall only does what Steve suggests but a little more with regards to permissions, constraints, triggers and so on by finding those renaming the old table build a new table with the same settings as the old (and all the stuff I named) then moving the old data to the new table and dropping the old table. There is a lot you need to remember to make sure you put back if you do yourself. If none of the above apply then rename, build a new table with the old name and your change then insert your data in the new table and delete the old.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 31, 2002 at 12:58 pm
Thank you guys for your kindly help.
I have solved the problem. Just for in case,anyone wants to know. Here is what I did.
1. add new column to the table
2. copy data from old column into the new column
3.drop the old column
4.rename the new column with the original name.
June 3, 2002 at 3:58 am
doesnt this work
ALTER TABLE <Table>
ALTER COLUMN <Column> varchar(2000)
plain and simple!!!
June 3, 2002 at 5:05 am
GRN thnaks for pointing that out as I did forget, however you have to be carefull that the ALTER COLUMN matches exactly as the column is now or some things can change so EM does make it a bit easier. Example if the column was built as
[colx] [varchar] (200) NOT NULL
if you do
ALTER TABLE myTbl
ALTER COLUMN colx varchar(2000)
Then when run the column is now NULL enabled
also you have to keep the following in mind
From BOL
quote:
Specifies that the given column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel.The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
A replicated column.
Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.
Some data type changes may result in a change in the data. For example, changing an nchar or nvarchar column to char or varchar can result in the conversion of extended characters. For more information, see CAST and CONVERT. Reducing the precision and scale of a column may result in data truncation.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply