create procedure sys.sp_replscriptuniquekeywhereclause
(
@tabid int -- id of the table
,@artid int -- id of the article
,@prefix nvarchar(10) = '@c' -- prefix for the scripted column variables
,@suffix nvarchar(10) = null -- suffix for the scripted column variables
,@mode tinyint
-- 1 = insert custom proc,
-- 2 = upd custom proc non PK (VCALL),
-- 3 = upd custom proc non PK (XCALL),
-- 4 = compensating where all keys,
-- 5 = compensating where non PK keys,
-- 6 = refresh cursor all keys,
-- 7 = refresh cursor non PK keys
-- 8 = compensating where PK only
-- 9 = upd custom proc PK only (XCALL),
,@paramcount int = null -- Total number of parameters - needed for mode = 2 and 3
,@skipindexesonudtcolumns bit = 0
)
as
begin
set nocount on
declare @retcode int
,@indid int
,@is_primary_key bit
,@indkey int
,@qualname nvarchar(517)
,@colname sysname
,@var sysname
,@artcol int
,@thiscol int
,@cmd nvarchar(4000)
,@findexstarted bit
,@fisfirstindex bit
,@fskipcomputedcols bit
,@emptywhereclauselength int
-- constants
,@modeinscustproc tinyint
,@modeupdcustprocnonpkvcall tinyint
,@modeupdcustprocnonpkxcall tinyint
,@modecompensatingallkeys tinyint
,@modecompensatingnonpkkeys tinyint
,@modecompensatingpkonly tinyint
,@moderefreshcursordeclareallkeys tinyint
,@moderefreshcursordeclarenonpkkeys tinyint
,@modeupdcustprocpkonlyxcall tinyint
-- initialize
select @modeinscustproc = 1
,@modeupdcustprocnonpkvcall = 2
,@modeupdcustprocnonpkxcall = 3
,@modecompensatingallkeys = 4
,@modecompensatingnonpkkeys = 5
,@moderefreshcursordeclareallkeys = 6
,@moderefreshcursordeclarenonpkkeys = 7
,@modecompensatingpkonly = 8
,@modeupdcustprocpkonlyxcall = 9
-- security check - should be dbo or sysadmin
exec @retcode = sp_MSreplcheck_publish
if @@error != 0 or @retcode != 0
return (1)
-- process if the object is a table and has index
if (ObjectProperty(@tabid, 'IsTable') != 1) or (ObjectProperty(@tabid, 'TableHasIndex') != 1)
return (1)
-- Get the qualified name of the table
exec @retcode = sp_MSget_qualified_name @tabid, @qualname OUTPUT
if @@error != 0 or @retcode != 0 or @qualname is null
return (1)
-- @artid cannot be null
if (@artid is null)
return (1)
-- Check @mode
if (@mode not in (@modeinscustproc, @modeupdcustprocnonpkvcall, @modeupdcustprocnonpkxcall,
@modeupdcustprocpkonlyxcall, @modecompensatingallkeys, @modecompensatingnonpkkeys,
@moderefreshcursordeclareallkeys, @moderefreshcursordeclarenonpkkeys,
@modecompensatingpkonly))
begin
return (1)
end
-- validate @paramcount
if ((@mode in (@modeupdcustprocnonpkxcall,@modeupdcustprocpkonlyxcall)) and (@paramcount is null))
begin
return (1)
end
-- enumerate indices
-- The scripting will be done as follows :
-- A) all keys will include PK and all unique keys
-- where (pk1 = @cv and pk2 = @cw ...) or (ui1k1 = @cx and ui1k2 = @cy ...) or (u2k1 = @cz and ...) ...
-- B) non PK keys will use only the unique keys that are not part of PK
-- where (ui1k1 = @cx and ui1k2 = @cy ...) or (u2k1 = @cz and ...) ...
select @cmd = case when (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
then N' N'' where '
else N' where ' end
,@findexstarted = 0
,@fisfirstindex = 1
,@fskipcomputedcols = 1
set @emptywhereclauselength = datalength(@cmd)
declare #hcindid cursor local fast_forward for
select index_id, is_primary_key
from sys.indexes indexes
where object_id = @tabid
and is_unique = 1
and index_id > 0 and index_id < 255
and (@skipindexesonudtcolumns = 0 or not exists (select index_columns.index_id
from sys.index_columns index_columns
inner join sys.columns columns
on index_columns.object_id = columns.object_id
and index_columns.index_id = indexes.index_id
and index_columns.column_id = columns.column_id
and index_columns.object_id = @tabid
inner join sys.types types
on columns.system_type_id = types.system_type_id
and columns.user_type_id = types.user_type_id
and types.is_assembly_type = 1))
order by index_id asc
open #hcindid
fetch #hcindid into @indid, @is_primary_key
while (@@fetch_status != -1)
begin
-- If we are in (@modeupdcustprocnonpkvcall, @modeupdcustprocnonpkxcall, @modecompensatingnonpkkeys,
-- @moderefreshcursordeclarenonpklkeys) mode then skip processing the PK index.
-- If we are in (@modeupdcustprocpkonlyxcall, @modecompensatingpkonly) mode
-- skip processing the non PK index
if ((@mode in (@modeupdcustprocnonpkvcall, @modeupdcustprocnonpkxcall, @modecompensatingnonpkkeys,
@moderefreshcursordeclarenonpkkeys)) and (@is_primary_key = 1)
or (@mode in (@modeupdcustprocpkonlyxcall, @modecompensatingpkonly))
and (@is_primary_key = 0))
begin
-- fetch next unique index
fetch #hcindid into @indid, @is_primary_key
continue
end
-- Enumerate the keys in this index
select @indkey = 1
while (@indkey <= 16)
begin
-- get the column name for the key
select @colname = index_col(@qualname, @indid, @indkey)
if (@colname is null)
break
-- check if this column is enabled for replication
select @artcol = 0
exec sp_MSget_col_position @tabid, @artid, @colname, NULL, @artcol output, 0, NULL, @thiscol output, @fskipcomputedcols
if (@artcol > 0)
begin
-- check if we are scripting the first key for this index
if (@findexstarted = 1)
begin
select @cmd = case when (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
then @cmd + N' + N'' and '
else @cmd + N' and ' end
end
else
begin
-- check if we are scripting the first index.
if (@fisfirstindex = 0)
begin
select @cmd = @cmd + N' or '
end
else
begin
select @fisfirstindex = 0
end
-- set @findexstarted while processing the first key
select @findexstarted = 1
select @cmd = @cmd + N' ( '
end
-- script this column
if (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
begin
-- scripting a delete compensating command for synctran proc - we are building a dynamic string
-- case when @c is null then '[col] is null' else '[col] = @c' end
declare @ccoltype sysname
select @var = @prefix + cast(@thiscol as nvarchar(10))
if (@suffix is not null)
select @var = @var + @suffix
select @cmd = @cmd + N''' + case when ( ' + @var + N' is null) then N'' ' + sys.fn_replreplacesinglequote(quotename(@colname)) + N' is null '' else N'' ' + sys.fn_replreplacesinglequote(quotename(@colname))
exec sp_MSget_colinfo @tabid, @thiscol, @artid, 0, NULL, @ccoltype output
if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'varchar' )
select @cmd = @cmd + N' = '''''' + CAST( sys.fn_replreplacesinglequote(' + @var + N') collate database_default as varchar) + '''''''' end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar')
select @cmd = @cmd + N' = N'''''' + sys.fn_replreplacesinglequote(' + @var + N') collate database_default + '''''''' end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'char')
select @cmd = @cmd + N' = '''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @var + N') as nvarchar)) collate database_default + '''''''' end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar')
select @cmd = @cmd + N' = N'''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @var + N') as nvarchar)) collate database_default + '''''''' end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
select @cmd = @cmd + N' = '' + sys.fn_varbintohexstr(' + @var + N') collate database_default end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','decimal','numeric'))
select @cmd = @cmd + N' = '' + CAST( ' + @var + N' as nvarchar) end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('float','real'))
select @cmd = @cmd + N' = '' + CONVERT(nvarchar(60), ' + @var + N' , 2) end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
select @cmd = @cmd + N' = '' + CONVERT(nvarchar(40), ' + @var + N' , 2) end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
select @cmd = @cmd + N' = '''''' + CAST( ' + @var + N' as nvarchar(40)) + '''''''' end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
select @cmd = @cmd + N' = '''''' + CONVERT(nvarchar(40), ' + @var + N', 112) + N'' '' + CONVERT(nvarchar(40), ' + @var + N', 114) + '''''''' end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('date','time', 'datetime2', 'datetimeoffset'))
select @cmd = @cmd + N' = '''''' + CONVERT(nvarchar(40), ' + @var + N', 121) + '''''''' end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
select @cmd = @cmd + N' = '' + sys.fn_sqlvarbasetostr( ' + @var + N' ) collate database_default end '
else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'xml')
select @cmd = @cmd + N' = N'''''' + CAST(' + @var + N' as nvarchar(max)) + '''''''' end '
else
select @cmd = @cmd + N' = '' + CAST( ' + @var + N' as nvarchar) end '
end
else
begin
-- scripting for custom procs - static script
-- For publisher scripting - use @thiscol
-- For custom proc scripting - use @artcol
-- For Update custom proc scripting special cases
-- for XCALL - use variation of @artcol
-- for VCALL - use @artcol
select @var = case when (@mode in (@modeupdcustprocnonpkxcall,@modeupdcustprocpkonlyxcall))
then @prefix + cast((@artcol + @paramcount/2) as nvarchar(10))
when (@mode in (@moderefreshcursordeclareallkeys, @moderefreshcursordeclarenonpkkeys))
then @prefix + cast(@thiscol as nvarchar(10))
else @prefix + cast(@artcol as nvarchar(10)) end
if (@suffix is not null)
select @var = @var + @suffix
-- Does the column allow NULLs
if (columnproperty( @tabid , @colname , 'AllowsNull' ) = 1)
begin
-- static scripting should handle NULL valued column
-- ((<@var> is null and is null) or (<@var> is not null and = <@var>))
select @cmd = @cmd + N'((' + @var + N' is null and ' + quotename(@colname) + N' is null) or ('
+ @var + N' is not null and ' + quotename(@colname) + N' = ' + @var + N'))'
end
else
begin
-- static scripting does not need to check for NULL values
-- = <@var>
select @cmd = @cmd + quotename(@colname) + N' = ' + @var
end
-- special processing for @modeupdcustprocnonpkvcall, @modeupdcustprocnonpkxcall
if (@mode = @modeupdcustprocnonpkvcall)
begin
declare @bytepos int
,@bitpos int
select @bytepos = 1 + (@artcol-1) / 8
,@bitpos = power(2, (@artcol-1) % 8 )
select @cmd = @cmd + N' and (( substring(@bitmap,' + cast(@bytepos as nvarchar(10))
+ N',' + cast(@bytepos as nvarchar(10)) + N') & ' + cast(@bitpos as nvarchar(10)) + N') = '
+ cast(@bitpos as nvarchar(10)) + N') '
end
else if (@mode = @modeupdcustprocnonpkxcall)
begin
select @cmd = @cmd + N' and ' + @prefix + cast((@artcol + @paramcount/2) as nvarchar(10))
if (@suffix is not null)
select @cmd = @cmd + @suffix
select @cmd = @cmd + N' != ' + @prefix + cast(@artcol as nvarchar(10))
if (@suffix is not null)
select @cmd = @cmd + @suffix
end
end
-- transfer command string to table if too large
if (len(@cmd) > 3000)
exec sp_MSflush_command @cmd output, 1, 0
end
-- get the next key for the index
select @indkey = @indkey + 1
end
-- done with current index
if (@findexstarted = 1)
begin
select @findexstarted = 0
,@cmd = case when (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
then @cmd + N' + '' ) '
else @cmd + N' ) ' end
end
-- fetch next unique index
fetch #hcindid into @indid, @is_primary_key
end
close #hcindid
deallocate #hcindid
if @mode in (@modeinscustproc,@modeupdcustprocnonpkvcall,@modeupdcustprocnonpkxcall) and datalength(@cmd) = @emptywhereclauselength
begin
set @cmd = @cmd + N'(0=1)'
end
-- Final flush
if (@mode in (@modecompensatingallkeys, @modecompensatingnonpkkeys, @modecompensatingpkonly))
select @cmd = @cmd + N''''
if (len(@cmd) > 0)
exec sp_MSflush_command @cmd output, 1, 0
-- all done
return 0
end
