Alter table syntax in SQL Server

Sheonarayan
Posted by Sheonarayan under Sql Server category on | Views : 6302
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 http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in-database-tables/

To read more indepth on this topic, go to http://msdn.microsoft.com/en-us/library/ms190273.aspx

To rename column or table , see this http://msdn.microsoft.com/en-us/library/ms188351.aspx

Hope this will help

Comments or Responses

Login to post response