how do i check if a column exists in sql anywhere ?

[Former Member]

this is how i check if a table exists:

select 1 from systable where table_name = 'sometablename';

but how do i check if a column exists?

Thanks!

[Former Member · Accepted solution]

I guess this will do:

select 1 from syscolumn join systable on systable.table_id = syscolumn.table_id where table_name = 'MYTABLENAME' and column_name = 'MYCOLUMNNAME';

[Former Member · Accepted solution]

Here I have an update statement coded in such a fashion that it will only attempt to add the column if it doesn't already exist...

HTH

IF NOT EXISTS(SELECT 1 FROM SYS.SYSCOLUMNS WHERE creator = 'DBA' AND tname = 'Job' AND cname = 'ConcealedHauling') THEN

  ALTER TABLE "DBA"."Job" ADD "ConcealedHauling" INTEGER NULL DEFAULT 0;  

END IF;

[VolkerBarth · Accepted solution]

So your are implicitly asking for a new "IF NOT EXISTS" clause for ALTER TABLE ADD modifications, something like?

ALTER TABLE ADD IF NOT EXISTS column-name [ AS ] column-data-type [ new-column-attribute ... ]