create procedure sys.sp_scriptpkwhereclause
(
@src_objid int
,@artid int
,@prefix nvarchar(10) = N'@pkc'
,@artcolcounter tinyint = 0 -- 0 means it does column counting base on pk, 1 means based on article columns, 2 means uses column ordinal
,@publishertype tinyint=1 -- 1 = mssqlserver, 2 = heterogeneous
,@skipcomputedcolumn bit=1 -- article column counting will be use this
)
as
begin
declare @this_col int
,@art_col int
,@spacer nvarchar(10)
,@isset int
,@iscomputed int
,@ispkcol int
,@isvalidcolumn tinyint
,@cmd nvarchar(4000)
,@colname sysname
,@pubtypemssqlserver tinyint
,@pubtypeheterogeneous tinyint
-- create WHERE clause
select @art_col = 0
,@pubtypemssqlserver = 1
,@pubtypeheterogeneous = 2
,@spacer = N' '
,@cmd = N'where'
if (@publishertype = @pubtypemssqlserver)
begin
DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR
select column_id, name from sys.columns where object_id = @src_objid order by column_id asc
end
else if (@publishertype = @pubtypeheterogeneous)
begin
DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR
select colid, name from IHsyscolumns where id = @src_objid order by colorder asc
end
else
begin
raiserror(21402, 16, 3, '@publishertype')
return 1
end
OPEN hCColid
FETCH hCColid INTO @this_col, @colname
WHILE (@@fetch_status <> -1)
begin
--skipping columns with no name
if @colname is not null
begin
-- If @artcolcounter = 1, then the counter will be updated for each column
-- that (is in PK) or (is in the article that is not computed).
-- sp_scriptxupdproc or sp_scriptxdelproc call this SP with @artcolcounter = 1
-- If @artcolcounter = 0, then the counter will be updated for each PK column
-- If @artcolcounter = 2, then the counter will be column ordinal
-- We are scripting the PK columns - check if this is a PK column
exec @ispkcol = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
if @ispkcol = 1
begin
-- Increment the counter for this PK column
select @art_col = case when (@artcolcounter = 2) then @this_col else @art_col + 1 end
-- script the assignment
select @cmd = @cmd + @spacer + QUOTENAME(@colname) + N' = ' + @prefix + convert( nvarchar, @art_col )
select @spacer = N' and '
if len( @cmd ) > 3000
begin
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N' '
end
end
else
begin
-- not a PK column
if @artcolcounter = 1
begin
-- check if this column is in the article
exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
if @isset = 1
begin
-- Check if this is a computed column
select @iscomputed = case when ((@publishertype = @pubtypemssqlserver)
and columnproperty( @src_objid, @colname, 'IsComputed' ) = 1) then 1 else 0 end
-- increment counter if this column is in article and is not computed
-- if the column is computed and the option to skip computed column
-- is not set then increment the counter
if @iscomputed = 0
select @art_col = @art_col + 1
else if (@skipcomputedcolumn=0)
select @art_col = @art_col + 1
end
end
end
end
FETCH hCColid INTO @this_col, @colname
end
CLOSE hCColid
DEALLOCATE hCColid
insert into #proctext(procedure_text) values( @cmd )
end