SQL Server – How to check if column exists in table

Posted onAuthorHeelpBook

Send Us a Sign! (Contact Us!)
--> (Word) --> (PDF) --> (Epub) --> (Text)
--> (XML) --> (OpenOffice) --> (XPS)

SCENARIO

I need to add a specific column if it does not exist. I have something like this, but it always returns false:

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

How can I check if a column exists on a table in SQL Server?

SOLUTION

SQL Server 2005 onwards:

if Exists(select * from sys.columns where Name = N'columnName' and Object_ID = Object_ID(N'tableName'))
begin
-- Column Exists
end

Or, as an alternative, you could use a more concise version, using COL_LENGTH:

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

SOLUTION (Function Version)

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

Ity should work on both SQL Server 2000 & SQL Server 2005.

SOURCE

LINK (Stackoverflow.com)

LANGUAGE
ENGLISH