How to check if a column exists in a SQL Server table?

ID : 439

viewed : 164

Tags : sql-serversql-server-2008tsqlsql-server-2012sql-server-2016sql-server

Top 5 Answer for How to check if a column exists in a SQL Server table?

vote vote

97

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns            WHERE Name = N'columnName'           AND Object_ID = Object_ID(N'schemaName.tableName')) BEGIN     -- Column Exists END 

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL BEGIN     -- Column Exists END 
vote vote

89

A more concise version

IF COL_LENGTH('table_name','column_name') IS NULL BEGIN /* Column does not exist or caller does not have permission to view the object */ END 

The point about permissions on viewing metadata applies to all answers not just this one.

Note that the first parameter table name to COL_LENGTH can be in one, two, or three part name format as required.

An example referencing a table in a different database is

COL_LENGTH('AdventureWorks2012.HumanResources.Department','ModifiedDate') 

One difference with this answer compared to using the metadata views is that metadata functions such as COL_LENGTH always only return data about committed changes irrespective of the isolation level in effect.

vote vote

74

Tweak the below to suit your specific requirements:

if not exists (select                      column_name                from                      INFORMATION_SCHEMA.columns                where                      table_name = 'MyTable'                      and column_name = 'MyColumn')     alter table MyTable add MyColumn int 

Edit to deal with edit to question: That should work - take a careful look over your code for stupid mistakes; are you querying INFORMATION_SCHEMA on the same database as your insert is being applied to for example? Do you have a typo in your table/column name in either statement?

vote vote

61

Try this...

IF NOT EXISTS(   SELECT TOP 1 1   FROM INFORMATION_SCHEMA.COLUMNS   WHERE      [TABLE_NAME] = 'Employees'     AND [COLUMN_NAME] = 'EmployeeID') BEGIN   ALTER TABLE [Employees]     ADD [EmployeeID] INT NULL END 
vote vote

51

For the people who are checking the column existence before dropping it.

From SQL Server 2016 you can use new DIE statements instead of big IF wrappers

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name 

Top 3 video Explaining How to check if a column exists in a SQL Server table?

Related QUESTION?