Add a column to a table, if it does not already exist

I want to write a query for MS SQL Server that adds a column into a table. But I don't want any error display, when I run/execute the following query.

I am using this sort of query to add a table ...

IF EXISTS (
SELECT *
FROM sys.objects
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Person]')
AND TYPE IN (N'U')
)

But I don't know how to write this query for a column.

IF COL_LENGTH('table_name', 'column_name') IS NULL
BEGIN
ALTER TABLE table_name
ADD [column_name] INT
END

Another alternative. I prefer this approach because it is less writing but the two accomplish the same thing.

IF COLUMNPROPERTY(OBJECT_ID('dbo.Person'), 'ColumnName', 'ColumnId') IS NULL
BEGIN
ALTER TABLE Person
ADD ColumnName VARCHAR(MAX) NOT NULL
END

I also noticed yours is looking for where table does exist that is obviously just this

if COLUMNPROPERTY( OBJECT_ID('dbo.Person'),'ColumnName','ColumnId') is not null

IF NOT EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE
OBJECT_ID = OBJECT_ID(N'[dbo].[Person]') AND name = 'DateOfBirth')
BEGIN
ALTER TABLE [dbo].[Person] ADD DateOfBirth DATETIME
END

You can use a similar construct by using the `sys.columns` table io `sys.objects`.

IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID(N'[dbo].[Person]')
AND name = 'ColumnName'
)

Here's another variation that worked for me.

IF NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE upper(TABLE_NAME) = 'TABLENAME'
AND upper(COLUMN_NAME) = 'COLUMNNAME')
BEGIN
ALTER TABLE [dbo].[Person] ADD Column
END
GO

> EDIT:
Note that `INFORMATION_SCHEMA` views may not always be updated, use `SYS.COLUMNS` instead:

`IF NOT EXISTS (SELECT 1
FROM SYS.COLUMNS....`

IF NOT EXISTS (SELECT * FROM syscolumns
WHERE ID=OBJECT_ID('[db].[Employee]') AND NAME='EmpName')
ALTER TABLE [db].[Employee]
ADD [EmpName] VARCHAR(10)
GO

I Hope this would help. [More info][1]


[1]:

[To see links please register here]

When checking for a column in another database, you can simply include the database name:

IF NOT EXISTS (
SELECT *
FROM DatabaseName.sys.columns
WHERE object_id = OBJECT_ID(N'[DatabaseName].[dbo].[TableName]')
AND name = 'ColumnName'
)