Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSis_joinfilter_based_on_PK_UQ_constraints

  No additional text.


Syntax
-- Requires Certificate signature for catalog access
create procedure sys.sp_MSis_joinfilter_based_on_PK_UQ_constraints
	@pubid uniqueidentifier,
	@join_filter_id int,
	@dri_based bit = 0 output,
	@unique_constraint_based bit = 0 output
as
begin
	declare		@parent_nickname int
	declare		@child_nickname int
	declare		@parent_objid int
	declare		@child_objid int
	declare		@parent_column	sysname
	declare		@child_column	sysname
	declare		@parent_column_id	int
	declare		@child_column_id	int
	declare		@keycnt		int
	declare		@keyindex	int
	declare		@join_filterclause nvarchar(3000)
	declare		@retcode	smallint
	declare		@constid int
	declare		@join_unique int
	declare		@prev_constid int
	
	select @dri_based = 0, @unique_constraint_based = 0
		
	select @join_unique = join_unique_key,
			@join_filterclause = join_filterclause,
			@parent_nickname = join_nickname,
			@child_nickname = art_nickname
	from dbo.sysmergesubsetfilters
	where pubid = @pubid
	and @join_filter_id = join_filterid
	
	if @join_unique is null or @join_unique = 0
		return 0
		
	if @join_filterclause like '%<>%' or @join_filterclause like '%!=%'
		return 0
		
	select @parent_objid = objid from dbo.sysmergearticles where nickname = @parent_nickname
	select @child_objid = objid from dbo.sysmergearticles where nickname = @child_nickname
	
	select top 1 @constid = f.object_id,
	       @keycnt = (select count(*) from sys.foreign_key_columns k
						where k.constraint_object_id = f.object_id)
	from sys.foreign_keys f
	where f.parent_object_id = @child_objid
	and f.referenced_object_id = @parent_objid
	order by f.object_id
	
	while (@constid is not null)
	begin
	
		if ObjectProperty(@constid, 'CnstIsDisabled') = 1
			goto GetNextFKConstraint
			
		if ObjectProperty(@constid, 'CnstIsNotRepl') = 1
			goto GetNextFKConstraint
			
		-- Loop over keys, and check whether they exist in the join filter clause.
		set @keyindex = 1
	
		while @keyindex <= @keycnt
		begin
			/* Get the column names */
	
			select @child_column_id = parent_column_id, @parent_column_id = referenced_column_id
			from sys.foreign_keys f, sys.foreign_key_columns k
			where f.parent_object_id = @child_objid and
					f.referenced_object_id = @parent_objid and
					k.constraint_object_id = f.object_id and
					k.constraint_column_id = @keyindex

			select @child_column = name from sys.columns
			where object_id = @child_objid
			and column_id = @child_column_id

			select @parent_column = name from sys.columns
			where object_id = @parent_objid
			and column_id = @parent_column_id
	
			if sys.fn_MSisfilteredcolumn(@join_filterclause, @child_column, @child_objid) = 0
			begin
				goto GetNextFKConstraint
			end
					
			if sys.fn_MSisfilteredcolumn(@join_filterclause, @parent_column, @parent_objid) = 0
			begin
				goto GetNextFKConstraint
			end
					
			if exists (select * from sys.columns where object_id = @child_objid
						and name = @child_column and is_nullable = 1)
			begin
				goto GetNextFKConstraint
			end

			if exists (select * from sys.columns where object_id = @parent_objid
						and name = @parent_column and is_nullable = 1)
			begin
				goto GetNextFKConstraint
			end
					
			/* move on to the next key */
			set @keyindex = @keyindex + 1
		end

		select @dri_based = 1, @unique_constraint_based = 1
		break
		
GetNextFKConstraint:
	
		select @prev_constid = @constid
		select @constid = NULL
		
		select top 1 @constid = f.object_id,
			@keycnt = (select count(*) from sys.foreign_key_columns k
							where k.constraint_object_id = f.object_id)
		from sys.foreign_keys f
		where f.parent_object_id = @child_objid
		and f.referenced_object_id = @parent_objid
		and f.object_id > @prev_constid
		order by f.object_id
	end
	
	-- if we already know this is unique key based, just return.
	if @unique_constraint_based = 1
		return 0
		
	select top 1 @constid = object_id(constraint_name), @keycnt = count(*)
	       from sys.fn_MSconstraint_columns(@parent_objid)
	       where type in ('PK', 'UQ')
	group by object_id(constraint_name)
	order by object_id(constraint_name)
	
	while (@constid is not null)
	begin
	
		if ObjectProperty(@constid, 'CnstIsDisabled') = 1
			goto GetNextUQConstraint
		
		-- Loop over keys, and check whether they exist in the join filter clause.
		set @keyindex = 1
	
		select @parent_column_id = -1
		
		while @keyindex <= @keycnt
		begin
			/* Get the column names */
	
			select top 1 @parent_column_id = constraint_column_id
			from sys.fn_MSconstraint_columns(@parent_objid)
			where object_id(constraint_name) = @constid
			and constraint_column_id > @parent_column_id
			order by constraint_column_id

			select @parent_column = name from sys.columns
			where object_id = @parent_objid
			and column_id = @parent_column_id
				
			if sys.fn_MSisfilteredcolumn(@join_filterclause, @parent_column, @parent_objid) = 0
			begin
				goto GetNextUQConstraint
			end
									
			/* move on to the next key */
			set @keyindex = @keyindex + 1
		end

		select @unique_constraint_based = 1
		break
		
GetNextUQConstraint:
	
		select @prev_constid = @constid
		select @constid = NULL
		
		select top 1 @constid = object_id(constraint_name), @keycnt = count(*)
	       from sys.fn_MSconstraint_columns(@parent_objid)
	       where type in ('PK', 'UQ')
	       and object_id(constraint_name) > @prev_constid
		group by object_id(constraint_name)
		order by object_id(constraint_name)
		
	end
	
	return 0
end

 
Last revision 2008RTM
See also

  sp_MSaddmergetriggers_internal (Procedure)
sp_MSaddupdatetrigger (Procedure)
sp_MSdrop_rladmin (Procedure)
sp_MSgenerateexpandproc (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