views:

3451

answers:

4

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

+10  A: 
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME='tablename' AND COLUMN_NAME='columname' )
CMS
For reference: This also works in SQL Server 2005
Russell
+1  A: 

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'

A: 

If I know the table, I don't need to query right? How to find a column If we don't know in which table it is?

vinny
A: 
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.

Deepak Yadav