create procedure sys.sp_MSisallreplcolpk
(
@artid int
,@publishertype tinyint=1 -- 1 = mssqlserver, 2 = heterogeneous
)
as
begin
if (@publishertype = 1)
begin
-- mssqlserver publisher
declare @pkindid int, @indkey int, @replcol int, @objname sysname, @tabid int
select @tabid = objid from dbo.sysarticles where artid = @artid
if exists( select * from sys.objects where object_id = @tabid and type = 'V' )
begin
select @pkindid = 1
end
else
begin
select @pkindid = indid from dbo.sysindexes where id = @tabid and status & 2048 <> 0
end
select @indkey = 1
select @objname = QUOTENAME(schema_name(OBJECTPROPERTY(@tabid, 'SchemaId'))) collate database_default + N'.' collate database_default + QUOTENAME(object_name( @tabid )) collate database_default
while @indkey <= 16 and index_col( @objname, @pkindid, @indkey ) is not null
begin
if not exists (select sac.colid from sys.columns sc join dbo.sysarticlecolumns sac on sc.column_id = sac.colid
where sac.artid = @artid and sc.object_id = @tabid and sc.name = index_col( @objname, @pkindid, @indkey ) )
break
else
select @indkey = @indkey + 1
end
select @replcol = count(colid) from sysarticlecolumns where artid = @artid
if @replcol = @indkey - 1
return 1
end
else if (@publishertype = 2)
begin
-- heterogeneous publisher
declare @pkcount int
,@colcount int
SELECT @colcount = COUNT(*)
FROM IHcolumns
WHERE @artid = article_id
SELECT @pkcount = COUNT(*)
FROM IHcolumns ihc, IHpublishercolumns ihpc, IHpublishercolumnconstraints ihpcc,
IHpublisherconstraints ihpcn, IHarticles iha
WHERE ihpcn.publisher_id = iha.publisher_id
and ihpcn.table_id = iha.table_id
and ihc.publishercolumn_id = ihpc.publishercolumn_id
and ihpc.publishercolumn_id = ihpcc.publishercolumn_id
and ihpcn.publisherconstraint_id = ihpcc.publisherconstraint_id
and iha.article_id = @artid
and ihpcn.type = 'PRIMARYKEY'
if @pkcount = @colcount
return 1
end
else
begin
raiserror(21402, 16, 16, '@publishertype')
end
-- return otherwise
return 0
end