SQL - Change a field length (nvarchar) using a script

BobBarker_99
BobBarker_99 used Ask the Experts™
on
Is it possible to change the field length of an nvarchar fields using a sql script?

If not, I suppose the only other way is to create a new table and copy everything over?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
ALTER TABLE tableNAme
ALTER COLUMN columnName nvarchar(4000)
what is the approx size of your data? are you using sql server 2005+? if yes, than I would recommend you to use.


Alter Table TableName
Alter Column ColumnName NVarchar(max)
Top Expert 2010

Commented:
BobBarker_99 said:
>>Is it possible to change the field length of an nvarchar fields using a sql script?

Yes you can, but...

1) That column cannot be part of a primary key or foreign key
2) That column cannot be part of any other index, unless the basic data type does not change, and the
new column size >= old column size

an example, where we want the new size to be 1000 and to allow nulls:

ALTER TABLE SomeTable ALTER COLUMN SomeColumn nvarchar(1000) NULL
Top Expert 2010

Commented:
aneesh and Ritesh,

I did see your posts; I only added mine because of the additional info regarding keys and indices :)

Cheers,

Patrick

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial