The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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!

asked 02 Mar '15, 06:06

vdcey's gravatar image

vdcey
619212735
accept rate: 33%


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';

permanent link

answered 02 Mar '15, 06:24

vdcey's gravatar image

vdcey
619212735
accept rate: 33%

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;
(03 Mar '15, 09:58) harncw
Replies hidden
1

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 ... ]
(03 Mar '15, 10:08) Volker Barth

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
     - D: 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
permanent link

answered 02 Mar '15, 06:15

Giorgio%20Papagno's gravatar image

Giorgio Papagno
3062410
accept rate: 20%

edited 02 Mar '15, 06:31

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645

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

thumbs up

(02 Mar '15, 06:27) vdcey
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×404

question asked: 02 Mar '15, 06:06

question was seen: 1,756 times

last updated: 03 Mar '15, 10:09