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 ... ]

[Former Member]

Here a store procedure to find objects in database:

create procedure dba.sp_exists_obj (@as_type varchar(2),
                                    @as_owner varchar(10),
                                    @as_name varchar(256),
                                    @ai_errcode integer output)
/*
Arguments :
- @as_type: type of object
    - P: stored procedure
    - V: view
    - T: trigger
    - U: user table
    - C: column
    - I: index
    - F: foreign key
     - 😧 domain
     - R: domain rule (solo per MS SQL Server)
     - L: domain default (solo per MS SQL Server)
     - N: funzioni ([STF] 26/09/2008: aggiunto)
     - E: eventi
- @as_owner: owner of objects
- @as_name: name  (i type 'C', use 'table.field')
- @ai_errcode: errorcode

Error code result:
0 - object not exists
1 - object exists */

as
begin
    declare @ls_table   CHAR(128),
    @ls_column  CHAR(128),
    @li_dot     INTEGER

    select @ai_errcode = 0

    if @as_type = 'P' or @as_type = 'N' begin
        if exists(
            select
                1
            from
                sys.sysprocedure
                    join sys.sysuserperm
                        on sys.sysprocedure.creator = sys.sysuserperm.user_id
            where
                sys.sysuserperm.user_name = @as_owner and
                sys.sysprocedure.proc_name = @as_name) begin
            select @ai_errcode = 1
        end
    end
    else if @as_type = 'T' begin
        if exists(
            select
                1
            from
                sys.systrigger
                    join sys.systable
                        on sys.systrigger.table_id = sys.systable.table_id
                    join sys.sysuserperm
                        on sys.systable.creator = sys.sysuserperm.user_id
            where
                sys.sysuserperm.user_name = @as_owner and
                sys.systrigger.trigger_name = @as_name) begin
            select @ai_errcode = 1
        end
    end
    else if @as_type = 'U' begin
        if exists(
            select
                1
            from
                sys.systable
                join sys.sysuserperm
                    on sys.systable.creator = sys.sysuserperm.user_id
            where
                sys.sysuserperm.user_name = @as_owner and
                sys.systable.table_name = @as_name and
                sys.systable.table_type = 'BASE') begin
            select @ai_errcode = 1
        end
    end
    else if @as_type = 'V' begin
        if exists(
            select
                1
            from
                sys.systable
                join sys.sysuserperm
                    on sys.systable.creator = sys.sysuserperm.user_id
            where
                sys.sysuserperm.user_name = @as_owner and
                sys.systable.table_name = @as_name and
                sys.systable.table_type = 'VIEW') begin
            select @ai_errcode = 1
        end
    end
    else if @as_type = 'C' begin
        select @li_dot = CharIndex('.', @as_name)

        if @li_dot > 0 begin
            select @ls_table  = left(@as_name, @li_dot - 1)
            select @ls_column = right(@as_name, length(@as_name) - @li_dot)

            if exists(
                select
                    1
                from
                    sys.systable
                    join sys.syscolumn
                        on sys.syscolumn.table_id = sys.systable.table_id
                    join sys.sysuserperm
                        on sys.systable.creator = sys.sysuserperm.user_id
                where
                    sys.sysuserperm.user_name = @as_owner and
                    sys.systable.table_name = @ls_table and
                    sys.syscolumn.column_name = @ls_column) begin

                select @ai_errcode = 1
            end
        end
    end
    else if @as_type = 'I' begin
        select @li_dot = charindex('.', @as_name)
        if @li_dot > 0 begin
            select
                @ls_table  = left(@as_name, @li_dot - 1),
                @ls_column = right(@as_name, length(@as_name) - @li_dot)

            if exists(
                select
                    1
                from
                    sys.sysindex
                        join sys.systable
                            on sys.sysindex.table_id = sys.systable.table_id
                where
                    sys.sysindex.creator = 1 and
                    sys.sysindex.index_name = @ls_column and
                    sys.systable.table_name = @ls_table) begin

                select @ai_errcode = 1
            end
        end
        else begin
            if exists(
                select
                    1
                from
                    sys.sysindex
                where
                    sys.sysindex.creator = 1 and
                    sys.sysindex.index_name = @as_name) begin

                select @ai_errcode = 1
            end
        end
    end
    else if @as_type = 'D' begin
        if exists(
            select
                1
            from
                sys.sysusertype
                    join sys.sysuserperm
                        on sys.sysusertype.creator = sys.sysuserperm.user_id
            where
                sys.sysuserperm.user_name = @as_owner and
                sys.sysusertype.type_name = @as_name) begin
            select @ai_errcode = 1
        end
    end
    else if @as_type = 'F' begin
        select @li_dot = charindex('.', @as_name)
        if @li_dot > 0 begin
            select
                @ls_table  = left(@as_name, @li_dot - 1),
                @ls_column = right(@as_name, length(@as_name) - @li_dot)

            if exists(
                select
                    1
                from
                    sys.sysforeignkeys
                where
                    sys.sysforeignkeys.foreign_creator = @as_owner and
                    sys.sysforeignkeys.foreign_tname = @ls_table and
                    sys.sysforeignkeys.role = @ls_column) begin
                select @ai_errcode = 1
            end
        end
        else begin
            if exists(
                select
                    1
                from
                    sys.sysforeignkeys
                where
                    sys.sysforeignkeys.foreign_creator = @as_owner and
                    sys.sysforeignkeys.role = @as_name) begin
                select @ai_errcode = 1
            end
        end
    end
    else if @as_type = 'E' begin
        if exists(
            select
                1
            from
                sys.sysevent
            where
                sys.sysevent.event_name = @as_name ) begin
            select @ai_errcode = 1
        end
    end
end
go

examples:

/* to add a column if not exists */
begin tran tr_obj_ex
    declare @li_esiste integer
    execute dba.sp_exists_obj 'C', 'dba', '<nome tabella="">.<nome colonna="">', @li_esiste output
    if @li_esiste = 0 begin
        alter table dba.<nome tabella="">
            add <nome colonna=""> <domain> null
    end
commit tran tr_obj_ex
go

[Former Member]

thanks for your answer, I deducted correct syntax from your answer.

thumbs up