Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_repltablehasnonpkuniquekey

  No additional text.


Syntax
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

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSisnonpkukupdateinconflict (Procedure)
sp_MSscriptdelconflictfinder (Procedure)
sp_MSscriptinsertconflictfinder (Procedure)
sp_MSscriptupdateconflictfinder (Procedure)
sp_MSscript_compensating_insert (Procedure)
sp_MSscript_insert_pubwins (Procedure)
sp_MSscript_insert_subwins (Procedure)
sp_MSscript_update_pubwins (Procedure)
sp_MSscript_update_subwins (Procedure)
sp_scriptupdproccore (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash