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'; 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?
(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 thanks for your answer, I deducted correct syntax from your answer. thumbs up
(02 Mar '15, 06:27)
vdcey
|