HEELPBOOK - SQL Server - How to check if column exists in table ######################

############# 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


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



############ ARTICLE INFO #############

Article Month: May
Article Date: 18/05/2012
Permalink: http://heelpbook.altervista.org/2012/sql-server-how-to-check-if-column-exists-in-table/
Source: http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook