How to add a new column to an existing table in MSSQL Server?

To add a new column to an existing table in MSSQL Server you can use the following snippet.

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

if you need to check if the column already exists you can use the following snippet.

IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name] = N'columnName' AND [object_id] = OBJECT_ID(N'tableName'))
BEGIN
    -- Add your column
END

Sample MSSQL

IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name] = N'CreationDate' AND [object_id] = OBJECT_ID(N'Testtable'))
BEGIN
    ALTER TABLE dbo.Testtable
	ADD CreationDate DATETIME NOT NULL
	CONSTRAINT DF__Testtable__CreationDate DEFAULT GETDATE()
END

see also ALTER TABLE (Transact-SQL), Check If Column Exists in SQL Server Table, Working with Default Constraints

Please Share us:
Please rate this snippet

One thought on “How to add a new column to an existing table in MSSQL Server?

Leave a Reply