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

Yet another variation...

SELECT
Count(*) AS existFlag
FROM
sys.columns
WHERE
[name] = N 'ColumnName'
AND [object_id] = OBJECT_ID(N 'TableName')

if exists (
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = '<table_name>'
and COLUMN_NAME = '<column_name>'
) begin
print 'Column you have specified exists'
end else begin
print 'Column does not exist'
end

Execute the below query to check if the column exists in the given table:

IF(SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') IS NOT NULL
PRINT 'Column Exists in the given table';

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

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?

Try something like:

CREATE FUNCTION ColumnExists(@TableName varchar(100), @ColumnName varchar(100))
RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
BEGIN
SET @Result = 'T'
END
ELSE
BEGIN
SET @Result = 'F'
END
RETURN @Result;
END
GO

GRANT EXECUTE ON [ColumnExists] TO [whoever]
GO

Then use it like this:

IF ColumnExists('xxx', 'yyyy') = 'F'
BEGIN
ALTER TABLE xxx
ADD yyyyy varChar(10) NOT NULL
END
GO

It should work on both [SQL Server 2000][1] and [SQL Server 2005][2]. I am not sure about [SQL Server 2008][3], but I don't see why not.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

I'd prefer `INFORMATION_SCHEMA.COLUMNS` over a system table because Microsoft does not guarantee to preserve the system tables between versions. For example, `dbo.syscolumns` does still work in SQL Server 2008, but it's deprecated and could be removed at any time in future.

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`][1] 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.

[1]:

[To see links please register here]

This worked for me in SQL Server 2000:

IF EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_name'
AND column_name = 'column_name'
)
BEGIN
...
END

I needed something similar for SQL Server 2000 and, as [Mitch points out][1], this only works in SQL Server 2005 or later.

This is what worked for me in the end:

if exists (
select *
from
sysobjects, syscolumns
where
sysobjects.id = syscolumns.id
and sysobjects.name = 'table'
and syscolumns.name = 'column')

[1]:

[To see links please register here]

A good friend and colleague of mine showed me how you can also use an `IF` block with SQL functions `OBJECT_ID` and `COLUMNPROPERTY` in [SQL Server 2005][1] and later to check for a column. You can use something similar to the following:

[You can see for yourself here][2]:

IF (OBJECT_ID(N'[dbo].[myTable]') IS NOT NULL AND
COLUMNPROPERTY( OBJECT_ID(N'[dbo].[myTable]'), 'ThisColumnDoesNotExist', 'ColumnId') IS NULL)
BEGIN
SELECT 'Column does not exist -- You can add TSQL to add the column here'
END

[1]:

[To see links please register here]

[2]:

[To see links please register here]