In many circumstances columns in a table can be removed, added or renamed without having to
drop and recreate the table.
By way of example I’ll create a table and then populate it with a couple of rows of data :
CREATE
TABLE Table1
(
Column1 INT NOT NULL,
Column2 VARCHAR(5) NULL,
Column3 VARCHAR(20) NOT NULL,
Column4 VARCHAR(50) NOT NULL,
Column5 DATETIME NOT NULL
)
GO
INSERT
INTO Table1 (Column1,Column2,Column3,Column4,Column5)
SELECT
1,'Mr','Fred','Bloggs','23 Dec 1962' UNION
SELECT
2,'Mrs','Freda','Bloggs','10 Jan 1964'
Adding Columns
To add columns simply define the column name and datatype (e.g. VARCHAR(100)) with the following syntax :
ALTER TABLE Table1
ADD Column6 VARCHAR(100) NULL
There are some restrictions around adding columns, for instance the column has to be NULLable if it doesn't have a default defined.
Deleting Columns
A similar syntax to that above can be used to drop an existing column :
ALTER TABLE Table1
DROP COLUMN
Column2
Renaming Columns
You can also rename an existing column, though curiously this is not supported by the
ALTER TABLE
syntax and instead we will need to use the sp_rename stored procedure :
exec sp_rename 'Table1.Column1','ID','COLUMN'
exec sp_rename 'Table1.Column5','DateOfBirth','COLUMN'
The last parameter specifies the object type to be renamed, in this case a column. There are
circumstance where columns cannot be dropped or renamed, for instance you can't rename
(or indeed delete) a column if it has a constraint defined on it.
You'll notice the following warning message when running sp_rename in SQL Server Management Studio.
However there's nothing to worry about, this is only a warning and the column will still get renamed!
