-- Requires Certificate signature for catalog access
create procedure sys.sp_MSadd_merge_partition_column (@function_name sysname) as
begin
declare @column_name sysname
, @index_name nvarchar(258)
, @function_name_with_parens nvarchar(260)
, @quoted_column_name nvarchar(260)
-- Max size for function name is 98 to fit in @index_name
if len(ltrim(rtrim(@function_name))) > 98
return 1
select @column_name = ltrim(rtrim(@function_name)) + '_FN'
select @index_name = 'ncMSmerge_partition_groups_' + @column_name
select @index_name = quotename(@index_name)
select @function_name_with_parens = ltrim(rtrim(@function_name)) + '()'
-- We do not allow two or more functions of the same name. So, do a case-insensitive comparison
-- of the corresponding column name.
if not exists (select * from sys.columns where UPPER(name) = UPPER(@column_name) and object_id = OBJECT_ID('dbo.MSmerge_partition_groups'))
begin
declare @cmd nvarchar(max), @colname sysname, @typestring nvarchar(4000)
select @cmd = N'
declare @objid int
select @function_name_with_parens as function_return_value into #temp_function_return_value
select @objid = OBJECT_ID(''tempdb..#temp_function_return_value'')
exec sys.sp_MSget_type_wrapper @objid, 1, @colname OUTPUT, @typestring OUTPUT
drop table #temp_function_return_value'
exec sys.sp_executesql @cmd, N'@function_name_with_parens nvarchar(260), @colname sysname OUTPUT, @typestring nvarchar(4000) OUTPUT', @function_name_with_parens, @colname OUTPUT, @typestring OUTPUT
if (@typestring is null or rtrim(ltrim(@typestring)) = ' ')
begin
-- raiserror
return 1
end
select @quoted_column_name = quotename(@column_name)
exec ('alter table dbo.MSmerge_partition_groups add ' + @quoted_column_name + ' ' + @typestring + ' NULL')
exec ('create index ' + @index_name + ' on dbo.MSmerge_partition_groups(' + @quoted_column_name + ')')
end
return 0
end