Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_create_objects

  No additional text.


Syntax
create procedure [sys].[sp_cdc_create_objects]
as
begin
	declare @stmt nvarchar(max)
		,@retcode int
	
    set nocount on

    -- Create the change data capture metadata tables
    -- with their associated indexes
    set @stmt =
		N'
		create schema [cdc] authorization [cdc]	
		create table [change_tables]					
		(											
			object_id			int,				
			version				int,				
			source_object_id	int,				
			capture_instance	sysname not null,			
			start_lsn			binary(10) null,			
			end_lsn				binary(10) null,			
			supports_net_changes	bit,			
			has_drop_pending	bit,				
			role_name			sysname null,			
			index_name			sysname null,
			filegroup_name		sysname null,
			create_date			datetime,
			partition_switch	bit default 0 not null,		
		    constraint [change_tables_clustered_idx]	
		    primary key								
			(										
				object_id	ASC						
			)						
		)											
		create table [ddl_history]				
		(
			source_object_id	int,											
			object_id			int,				
			required_column_update	bit,		
			ddl_command			nvarchar(max),		
			ddl_lsn				binary(10),			
			ddl_time			datetime,			
		    constraint [ddl_history_clustered_idx]
			primary key								
			(
				object_id	ASC,					
				ddl_lsn		ASC								
			)												
		)											
		create table [lsn_time_mapping]			
		(											
			start_lsn			binary(10),			
			tran_begin_time		datetime,			
			tran_end_time		datetime,			
			tran_id				varbinary(10),
			tran_begin_lsn			binary(10),
			constraint [lsn_time_mapping_clustered_idx]		
			primary key								
			(										
				start_lsn	ASC						
			)												
		)											
		create table [captured_columns]			
		(											
			object_id			int,				
			column_name			sysname,
			column_id			int,			
			column_type			sysname,			
			column_ordinal		int,
			is_computed			bit,				
			constraint [captured_columns_clustered_idx]	
			primary key								
			(										
				object_id		ASC,				
				column_ordinal	ASC					
			)												
		)											
		create table [index_columns]				
		(											
			object_id			int,				
			column_name			sysname,				
			index_ordinal		tinyint,
			column_id			int,			
			constraint [index_columns_clustered_idx]	
			primary key								
			(										
				object_id	ASC,					
				index_ordinal	ASC						
			)												
		);										'
	
	exec (@stmt)	

    -- Mark all of these tables as system objects in order for them to show up properly in sqlwb
    exec sp_MS_marksystemobject N'cdc.change_tables'

    exec sp_MS_marksystemobject N'cdc.ddl_history'

    exec sp_MS_marksystemobject N'cdc.lsn_time_mapping'

    exec sp_MS_marksystemobject N'cdc.captured_columns'

    exec sp_MS_marksystemobject N'cdc.index_columns'

    -- Create unique index on capture_instance for the change_tables table
    create unique index [change_tables_unique_idx] on [cdc].[change_tables] (  capture_instance ASC )

    -- Create non-unique index on source_object_id for the change_tables table
    create index [source_object_id_idx] on [cdc].[change_tables] (  source_object_id ASC )

    -- Create non-unique index on tran_end_time for the lsn_time_mapping table
    create index [lsn_time_mapping_nonunique_idx] on [cdc].[lsn_time_mapping] (  tran_end_time ASC )

    -- Grant select on the lsn_time_mapping table to public
    grant select on [cdc].[lsn_time_mapping] to public

	-- Create dummy functions used to generate appropriate errors
	-- when fn_cdc_check_parameters encounters both access and
	-- range errors when called from the TVFs.
	set @stmt = N'
	create function cdc.[fn_cdc_get_all_changes_...](
		@from_lns binary(10),
		@to_lsn binary(10),
		@row_filter_options nvarchar(30)
	)
	returns table
	return	
		select 0 as ''col'' '

	exec (@stmt)
	
	exec sp_MS_marksystemobject N'cdc.[fn_cdc_get_all_changes_...]'
	
	set @stmt = N'
	create function cdc.[fn_cdc_get_net_changes_...](
		@from_lns binary(10),
		@to_lsn binary(10),
		@row_filter_options nvarchar(30)
	)
	returns table
	return	
		select 0 as ''col'' '
	
	exec (@stmt)
	
	exec sp_MS_marksystemobject N'cdc.[fn_cdc_get_net_changes_...]'

	-- The grant to this version of these dummy functions is to public.
	-- This is used to generate the error returned for range errors
	-- as opposed to access errors.
	set @stmt = N'
	create function cdc.[fn_cdc_get_all_changes_ ... ](
		@from_lns binary(10),
		@to_lsn binary(10),
		@row_filter_options nvarchar(30)
	)
	returns table
	return	
		select 0 as ''col'' '
	
	exec (@stmt)
																
	exec sp_MS_marksystemobject N'cdc.[fn_cdc_get_all_changes_ ... ]'

	set @stmt = N'
	create function cdc.[fn_cdc_get_net_changes_ ... ](
		@from_lns binary(10),
		@to_lsn binary(10),
		@row_filter_options nvarchar(30)
	)
	returns table
	return	
		select 0 as ''col'' '
	
	exec (@stmt)
																
	exec sp_MS_marksystemobject N'cdc.[fn_cdc_get_net_changes_ ... ]'

	-- grant select to public
	grant select on cdc.[fn_cdc_get_all_changes_ ... ] to public

	grant select on cdc.[fn_cdc_get_net_changes_ ... ] to public

    return 0

end

 
Last revision 2008RTM
See also

  sp_cdc_enable_db_internal (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