Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

To find if column exists 1

Status
Not open for further replies.

hf28

Programmer
Oct 23, 2003
54
US
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
 
if exists ( select col_name from syscolumns where object_name(id) = 'tbname')
 
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
 
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
 
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
 
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
 
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
 
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.
 
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
 
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.
 
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.
 
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
 
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.
 
Try this:

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

-SQLBill
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top