-- Name: sp_MSmergefixup_constraints_name
-- Owner: QunGuo
-- Description: this is a helper procedure which is used by ddl trigger to
-- handle the condition where user did not provide explicit constraint
-- name, by retrieving constraint name from system catelog view
-- it covers CHECK, UNIQUE, FOREIGN KEY, and works off
-- prepopulated #merge_altertable table
-- Parameter:
-- Returns: 1 or 0 0 = success
-- Security: internal, not exposed
-- Requires Certificate signature for catalog access
create procedure sys.sp_MSmergefixup_constraints_name
(
--pass in these constant from caller, so we don't have define them everwhere
@schemasubtype_addun int
,@schemasubtype_addfk int
,@schemasubtype_addchk int
,@objid int
,@debug_print bit = 0
)
as
begin
declare @colname nvarchar(512)
,@typeid nvarchar(32)
,@modified_date datetime
,@row_id int
,@type_mask int
,@done_mask int
declare @subtype_addfk int
,@subtype_addun int
,@subtype_addchk int
begin tran fixup_constraints_name
save tran fixup_constraints_name
set @subtype_addun = 0x8 --N'ADDUNIQUE'
set @subtype_addfk = 0x4 --N'ADDREFERENCE'
set @subtype_addchk = 0x10 --N'ADDCHECK'
set @done_mask = @subtype_addun | @subtype_addfk | @subtype_addchk
DECLARE #fkcursor CURSOR LOCAL FAST_FORWARD for
select name, type, modify_date from sys.key_constraints
where parent_object_id = @objid and
type = 'UQ' collate database_default
union
select name, type, modify_date from sys.foreign_keys
where parent_object_id = @objid
union
select name, type, modify_date from sys.check_constraints
where parent_object_id = @objid
order by modify_date desc
open #fkcursor
fetch #fkcursor into @colname, @typeid, @modified_date
while @@fetch_status <> -1
begin
if @debug_print = 1
select 'stage' = 'sp_MSmergefixup_constraints_name:query'
,'@colname' = @colname
,'@typeid' = @typeid
,'@modified_date' = @modified_date
,'@type_mask' = @type_mask
,'@done_mask' = @done_mask
set @row_id = NULL
select top 1 @row_id = id
from #merge_altertable where
schemasubtype = case @typeid when N'UQ' then @schemasubtype_addun
when N'F' then @schemasubtype_addfk
when N'C' then @schemasubtype_addchk
end
and len(isnull(ColumnName, '')) = 0 order by id desc
if @row_id is null
begin
--exit if we've finished processing all empty names for un/fk/chk
if (@type_mask & @done_mask) = @done_mask
break
else
select @type_mask = @type_mask |
case @typeid when N'UQ' then @schemasubtype_addun
when N'F' then @schemasubtype_addfk
when N'C' then @schemasubtype_addchk
end
end
else
begin
if @debug_print = 1
select 'stage' = 'sp_MSmergefixup_constraints_name:update'
,'@row_id' = @row_id
, * from #merge_altertable where id = @row_id
update #merge_altertable set ColumnName = @colname where id = @row_id
if @@error > 0
goto FAILURE
end
fetch #fkcursor into @colname, @typeid, @modified_date
end
close #fkcursor
deallocate #fkcursor
if @debug_print = 1
select 'stage' = 'sp_MSmergefixup_constraints_name:exit'
,'comments' = 'un/fk/chk name filled'
,* from #merge_altertable
commit tran fixup_constraints_name
return 0
FAILURE:
close #fkcursor
deallocate #fkcursor
rollback tran fixup_constraints_name
commit tran
return 1
end