CODEWITHSUNDEEP
sqlsql-server
leora
leora

Reputation: 196861

What is the SQL to change the field length of a table column in SQL Server

What is the SQL to make a field go from nvarchar(50) to nvarchar(250)?

When I try to change it through the SQL Server Management Studio, it doesn't allow me to do it, so I figured I would try SQL directly instead of using the GUI.

Upvotes: 27

Views: 129222

Answers (6)

Javed Ansari
Javed Ansari

Reputation: 27

For Oracle SQL Developers

Alter table tblname MODIFY (colname varchar2(250));

Description : It will increase the length of column. where 250 represent the updated (incremented) length of column.

Upvotes: -1

Randy Minder
Randy Minder

Reputation: 48522

ALTER TABLE MyTable
ALTER COLUMN MyColumn varchar(NewSize)

Upvotes: 12

Dillip
Dillip

Reputation: 19

To change the datatype of many column but same datatype

alter table employee modify (firstname varchar2(9),lastname varchar2(9),email varchar2(9));
-- Table altered.


alter table employee modify (firstname,lastname,email varchar2(9));
-- Table altered.

Upvotes: 0

Olaolu Ajose
Olaolu Ajose

Reputation: 23

Its sometimes safer to check if the table exist in the first place...

IF COL_LENGTH('[tablename]','[tablecolumn]') IS NULL
        BEGIN
        ALTER TABLE tablename
            ALTER COLUMN [tablecolumn] 
            NVARCHAR(500)  
        END

Upvotes: 2

saravanan
saravanan

Reputation: 1092

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:

SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN column_name datatype

My SQL / Oracle (prior version 10G):

ALTER TABLE table_name MODIFY COLUMN column_name datatype

Oracle 10G and later:

ALTER TABLE table_name
MODIFY column_name datatype

Upvotes: 9

Martin Smith
Martin Smith

Reputation: 453898

Alter table tblname ALTER Column colname nvarchar(250) [NOT] NULL

If NULL / NOT NULL is not specified the column will become Nullable irrespective of what ever the original specification was.

Upvotes: 59

Related Questions