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