To find if column exists 1

[hf28]

Hello everyone,

does anyone know how to write a query to find out if the column exists in the table?

I tried:

if exists (Select ColumnName from Table)

but it doesn't work.

Thanks for your help

 

[ClaireHCM]

if exists ( select col_name from syscolumns where object_name(id) = 'tbname')

 

[hf28]

Thanks a lot, Claire. I did it like this, but it didn't work.

if exists (select _010 from syscolumns where id = object_id(N'[dbo].[tblSummaryStatus]'))

It gives
"Incorrect syntax near ')'"

Error

 

[SQLBill]

Is your column name REALLY _10? Also, you didn't follow Claire's format, that's why you have a syntax error.

The syntax is: (italics is what YOU change)

IF EXISTS(SELECT column_name FROM SYSCOLUMNS WHERE OBJECT_NAME(ID) = 'table_name')

(You don't have to use the uppercase letters like I do)

-SQLBill

 

[ClaireHCM]

This is a if condition, you should specify what you want it to do after the if condition.


if exists (select _010 from syscolumns where id = object_id(N'[dbo].[tblSummaryStatus]'))
print 1

 

[hf28]

Thanks, Bill

Yes _010 is the column name

I did like you and Claire said

IF EXISTS(SELECT _010 FROM SYSCOLUMNS WHERE OBJECT_NAME(ID) = 'tblSummaryStatus')


Brings the same error

 

[ClaireHCM]

As I said, did you specify the condition after the if condition. You couldnt execute the if condition alone, you must specify the next step that after the if validation step

 

[hamzajosh]

Put a space after the first bracket and a space before last bracket. It should look like this now

IF EXISTS( SELECT _010 FROM SYSCOLUMNS WHERE OBJECT_NAME(ID) = 'tblSummaryStatus' )

It will work. I don't know why but in query analyzer, these spaces are required in all subqueries. Anyone tell me why would be helpful


Learn everything but implement only what is needed.

 

[hf28]

That's the whole thing:

IF EXISTS( SELECT _010 FROM SYSCOLUMNS WHERE OBJECT_NAME(ID) = 'tblSummaryStatus' )

BEGIN
PRINT 'Good column'

END
ELSE
PRINT 'Bye'

Now, it gives me an error :
invalid column name '_010'

though this column exists. If I say "Select 010 from tblSummaryStatus", it brings everything

 

[hamzajosh]

put column name in quotes

IF EXISTS( SELECT '_010' FROM SYSCOLUMNS WHERE OBJECT_NAME(ID) = 'tblSummaryStatus' )

BEGIN
PRINT 'Good column'

END
ELSE
PRINT 'Bye'



Learn everything but implement only what is needed.

 

[hf28]

Thanks. If you put column name in ' ' , it will bring the value. If you put it into " ", then it will be looking for a column, but will bring the same error.

 

[SQLBill]

quote: (bolding mine for emphasis)

invalid column name '_010'

though this column exists. If I say "Select 010 from tblSummaryStatus", it brings everything

unquote

_010 and 010 are NOT the same thing.

-SQLBill

 

[nigelrivett]

IF EXISTS( SELECT * FROM SYSCOLUMNS WHERE OBJECT_NAME(ID) = 'tblSummaryStatus' and name = '_010')
BEGIN
PRINT 'column exists'

END
ELSE
BEGIN
PRINT 'column doesn't exist'
END



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBill]

Try this:

IF EXISTS(SELECT NAME FROM SYSCOLUMNS WHERE NAME = '_010')
PRINT 'column found'
ELSE
PRING 'column not found'

-SQLBill