-- Name: sp_MSfixup_single_artddls
-- Owner: QunGuo
-- Description: this is a helper procedure which is used by ddl trigger to
-- reconstruct individual ddl cmd per article, more specifically,
-- it covers add/alter of non-computed columns
-- everything eles are covered in sp_MSfixup_single_ddls
-- Parameter:
-- Returns: 1 or 0 0 = success
-- Security: internal, not exposed
-- Requires Certificate signature for catalog access
create procedure sys.sp_MSfixup_single_artddls
(
@objid int
,@schema_option bigint
,@subtype_altercolumn int
,@subtype_addcolumn int
,@debug_print bit = 0
)
as
begin
begin tran replddl_fixup_single_artddls
save tran replddl_fixup_single_artddls
if @debug_print = 1
select 'stage' = 'sp_MSfixup_single_artddls:enter'
,'comments=' = 'have un/fk/chk/df(some)/computed col, but but not addcol/altercol'
, * from #tran_altertable
--update newly added columns entry now
update #tran_altertable set
stmt = quotename(c.name) + N' ' + sys.fn_gettypestring(c.object_id
,c.column_id
,1
,case (@schema_option & 0x0000000000000020) when 0x0000000000000020 then 1 else 0 end
,case (@schema_option & 0x0000000010000000) when 0x0000000010000000 then 1 else 0 end
,case (@schema_option & 0x0000000020000000) when 0x0000000020000000 then 1 else 0 end
,case (@schema_option & 0x0000000000000008) when 0x0000000000000008 then 0 else 1 end
,case (@schema_option & 0x0000000200000000) when 0x0000000200000000 then 1 else 0 end
,case (@schema_option & 0x0000002000000000) when 0x0000002000000000 then 1 else 0 end
,case (@schema_option & 0x0000001000000000) when 0x0000001000000000 then 1 else 0 end
,case (@schema_option & 0x0000008000000000) when 0x0000008000000000 then 1 else 0 end
) +
case when c.is_filestream = 1 and (@schema_option & 0x0000000120000000) = 0x0000000100000000 then
N' FILESTREAM'
else
N''
end +
case when c.is_sparse = 1 and (@schema_option & 0x0000020000000000)= 0x0000020000000000 then
N' SPARSE'
else
N''
end +
case c.is_identity when 1 then
case (@schema_option & 0x0000000000000004) when 0x0000000000000004 then
case c.is_nullable when 0 then N' NOT NULL'
else N' NULL'
end +
N' IDENTITY (' + cast(ic.seed_value as nvarchar(128)) + N',' + cast(ic.increment_value as nvarchar(128)) + N') NOT FOR REPLICATION'
--if we replicate without identity, can't set the column to 'NOT NULL'
else N' NULL'
end
else
case c.is_nullable when 0 then N' NOT NULL'
else N' NULL'
end
end +
case t.EnumType when @subtype_addcolumn then
ISNULL(N' CONSTRAINT ' + quotename(d.name) + N' DEFAULT ' + definition, N'') +
case t.ColumnAttr collate database_default when N'WITH VALUES' then N' WITH VALUES'
else N''
end
else N''
end
,column_id = c.column_id
from sys.columns c
left outer join sys.default_constraints d
on c.object_id = d.parent_object_id
and c.column_id = d.parent_column_id
left outer join sys.identity_columns ic
on ic.object_id = c.object_id
and ic.column_id = c.column_id
join #tran_altertable t on c.name = t.ColumnName collate database_default
where c.object_id = @objid and (t.EnumType & (@subtype_addcolumn | @subtype_altercolumn) > 0)
and c.column_id not in (select column_id from sys.computed_columns where object_id = @objid)
if @@ERROR<>0
goto FAILURE
if @debug_print = 1
select 'stage' = 'sp_MSfixup_single_artddls:exit'
,'comments=' = 'now with add/alter cols'
, * from #tran_altertable
commit tran single_cmds
return 0
FAILURE:
rollback tran single_cmds
commit tran
return 1
end