How do I check if a column exists in SQL Server?

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

If col_length('table_name','column_name') is null select 0 as Present ELSE select 1 as Present 

Present will be 0, is there is no column_name present in table_name , otherwise 1

@CMS: I don't think that 'INFORMATION_SCHEMA.COLUMNS' have information about every table in DB. Because this didn't worked for me. But my answer did worked.

In query analyzer, select the Database that contains the table in which you need to check if the field exists or not and run the query below.

SELECT count(*) AS [Column Exists] FROM SYSOBJECTS INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID WHERE SYSOBJECTS.NAME = 'myTable' AND SYSCOLUMNS.NAME = 'Myfield' 

This should do nicely:

if COLUMNPROPERTY(object_id('table_name'), 'column_name', 'ColumnId') is null print 'doesn\'t exist' else print 'exists' 

I don't know if this script will work in sqlserver 2000, but in 2008 works:

SELECT COLUMNS.* FROM INFORMATION_SCHEMA.COLUMNS COLUMNS, INFORMATION_SCHEMA.TABLES TABLES WHERE COLUMNS.TABLE_NAME=TABLES.TABLE_NAME AND UPPER(COLUMNS.COLUMN_NAME)=UPPER('column_name') 

This query will work in SQL Server 2000

select * from sys.columns where name = 'ColumnName'