Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_change_data_capture

  No additional text.


Syntax

create procedure sys.sp_cdc_change_data_capture
with execute as 'dbo'
as
begin
	set nocount on
	
	declare @captured_column_list nvarchar(max)
			,@source_object_id int
			,@object_id int
			,@column_name sysname
			,@quoted_db nvarchar(1000)
			,@capture_instance sysname
			,@index_column_list nvarchar(max)
			
	set @quoted_db     = quotename(db_name())
	
	-- This is a request for capture information on all source table that
	-- the caller has select access to
	declare #hinstance cursor local fast_forward for
		select capture_instance, object_id
		from  #capture_instances
			
	create table #index_column_list
	(
		object_id int,
		index_list nvarchar(max) collate database_default
	)
	create table #captured_column_list
	(
		object_id int,
		included_list nvarchar(max) collate database_default
	)
		
	open #hinstance
	fetch #hinstance into @capture_instance, @object_id

	while (@@fetch_status <> -1)
	begin
		
		declare #hindexcolumns cursor local fast_forward for
			select i.column_name
			from  [cdc].[index_columns] i
			inner join [cdc].[change_tables] c
				on i.object_id = c.object_id
			where c.capture_instance = @capture_instance
			order by i.index_ordinal

		open #hindexcolumns
		fetch #hindexcolumns into @column_name

		if (@@fetch_status <> -1)
		begin
			set @index_column_list = quotename(@column_name)
			fetch #hindexcolumns into @column_name
		end
	
		while (@@fetch_status <> -1)
		begin
			set @index_column_list = @index_column_list +
				N', ' + quotename(@column_name)		
			fetch #hindexcolumns into @column_name
		end

		insert into #index_column_list
		select @object_id, @index_column_list
	
		close #hindexcolumns
		deallocate #hindexcolumns

		declare #hcapturedcolumns cursor local fast_forward for
			select e.column_name
			from  [cdc].[captured_columns] e inner join [cdc].[change_tables] c
				on e.object_id = c.object_id
			where c.capture_instance = @capture_instance
				
		open #hcapturedcolumns
		fetch #hcapturedcolumns into @column_name

		if (@@fetch_status <> -1)
		begin
			set @captured_column_list = quotename(@column_name)
			fetch #hcapturedcolumns into @column_name
		end
	
		while (@@fetch_status <> -1)
		begin
			set @captured_column_list = @captured_column_list +
				N', ' + quotename(@column_name)		
			fetch #hcapturedcolumns into @column_name
		end

		insert into #captured_column_list
		select @object_id, @captured_column_list
	
		close #hcapturedcolumns
		deallocate #hcapturedcolumns

		fetch #hinstance into @capture_instance, @object_id
	end

	close #hinstance
	deallocate #hinstance

	select object_schema_name(c.source_object_id) as source_schema,
		object_name(c.source_object_id) as source_table,
		c.capture_instance, c.object_id, c.source_object_id,
		c.start_lsn, c.end_lsn,	c.supports_net_changes, c.has_drop_pending,
		c.role_name, c.index_name, c.filegroup_name, c.create_date,
		(select index_list from #index_column_list i
		 where i.object_id = c.object_id) as index_column_list,
		(select included_list from #captured_column_list e
		 where e.object_id = c.object_id) as captured_column_list
	from [cdc].[change_tables] c inner join #capture_instances t
		on c.capture_instance = t.capture_instance

	return 0
end																

 
Last revision 2008RTM
See also

  sp_cdc_help_change_data_capture (Procedure)
sp_MSdrop_cdc (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