How to check if column exists in SQL Server table

[Hadley]

Hello,

I want to check if column exists or not in SQL Server table and need to add a specific column if it does not exist.
I create a code as follows, but it always returns false:

IF EXISTS(SELECT * FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'myTableName'
                 AND COLUMN_NAME = 'myColumnName')

So, how can I check if a column exists in a table in SQL Server database?

[KelvinSmith]

Hi,

Execute the following query to check if column exists or not in SQL Server table.

IF EXISTS(SELECT * FROM sys.columns
WHERE Name = N'columnName' AND OBJECT_ID = OBJECT_ID(N'tableName'))
BEGIN
PRINT 'Your Column Exists'
END

To add a specific column if it does not exist in SQL Table.

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

[Nimbus]

You can also try:

IF COL_LENGTH('table_name','column_name') IS NOT NULL
BEGIN
PRINT 'Your Column Exists'
END