Alter table syntax in SQL Server

Posted by Sheonarayan under Sql Server category on | Views : 6584
The syntax is very simple,

ALTER TABLE {tablename}
ADD {fieldName} {datatype} -- add column with null value
ADD {fieldName} {datatype} not null default {default value} -- add column with not null value but with default value
DROP COLUMN {ColumnName}

-- Sample script
ALTER TABLE ProducttableName
ADD ProductTypeIdFieldName int

-- To add column with not null and default value
-- ADD ProductTypeIdd int not null default 0

-- Alter column of the table
-- ALTER COLUMN {ColumnName} {DataType} NULL

-- top drop column
-- DROP COLUMN ProductTypeId

Constraint: You can only add column that contains null values or you will have to specify default value for the column.

If you want to reposition the field in the database table, its little difficult, read this post for more details

To read more indepth on this topic, go to

To rename column or table , see this

Hope this will help

Comments or Responses

Login to post response