Need help with complex column creation command

Hello, all

I need help with a complex column creation command and SQL anywhere help is not sufficient for it.

Here is the situation:

I need to write a generic DDL "alter table" command, which can add/modify columns without knowing in advance if they already exist in the destination table.

Is there a command, which looks like:

alter table "table1" add (on existing modify) column1 <datatype> <default> ?

Thank you,

Arcady

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Oct 14, 2014 at 08:34 AM

    Hi.

    I don't think this is supported in alter table command. But you can code that inside an if statement which queries systables & syscolumns. Your code should be something like that:

    if (select count(*) from sysobjects, syscolumns where sysobjects.id = syscolumns.id and sysobjects.name = 'some_table' and syscolumns.name = 'some_column') < 1

    begin

        alter table some_table add some_column numeric(12) not null

    end

    This is an example..

    Andreas.

    Add comment
    10|10000 characters needed characters exceeded

    • Don't confuse "easy to use" with "easy to implement"; in fact, the exact opposite is often the case: it is often very difficult to implement a feature that is easy to use.

      Andreas has given you some old-school Transact SQL code to implement exactly what you are asking for (an IF EXISTS). Alternative code using the modern catalog views SYSTAB and SYSTABCOL will be different, but not any simpler. You will not find any simpler equivalent such as "IF EXISTS COLUMN c IN TABLE t" built in to SQL Anywhere... you have to build it yourself. Andreas has suggested packaging that logic in a function which is a good idea.

      Alternatively, you can as for a new feature; just post a new message here, and wait...

Skip to Content