create procedure sys.sp_repltablehasnonpkuniquekey
(
@tabid int -- id of the table
)
as
begin
set nocount on
declare @retcode int
-- 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) and (ObjectProperty(@tabid, 'TableHasIndex') = 1)
begin
-- Check for non PK unique keys
if exists (select indid from dbo.sysindexes
where id = @tabid
and indid > 0 and indid < 255
and (status & 2) != 0 and (status & 2048) = 0 )
begin
declare @indid int
,@indkey int
,@artid int
,@qualname nvarchar(517)
,@colname sysname
,@artcol int
-- initialize qualified name, columns
exec @retcode = sp_MSget_qualified_name @tabid, @qualname OUTPUT
if @@error != 0 or @retcode != 0 or @qualname is null
return -1
-- If this table is not replicated then return 0 (no unique key found)
select @artid = artid from dbo.sysarticles where objid = @tabid
if (@@error != 0 or @artid is null)
return 0
-- Ensure that the non PK unique key is being replicated
declare #hcindid cursor local fast_forward for
select indid from sysindexes
where id = @tabid
and indid > 0 and indid < 255
and (status & 2) != 0 and (status & 2048) = 0
order by indid asc
open #hcindid
fetch #hcindid into @indid
while (@@fetch_status != -1)
begin
-- 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
if (@artcol > 0)
begin
-- we have found a replicated non PK unique key column
-- break out of this loop
select @retcode = 1
break
end
-- get the next key for the index
select @indkey = @indkey + 1
end -- while (@indkey <= 16)
-- If we have found any replicated non PK unique key column
-- then we do not need to process any further
if (@retcode =1)
break
-- fetch next unique index
fetch #hcindid into @indid
end -- while (@@fetch_status != -1)
close #hcindid
deallocate #hcindid
end
end
-- all done
return @retcode
end