April 16, 2012

sp_cdc_enable_table (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_cdc_enable_table(nvarchar @source_schema
, nvarchar @source_name
, nvarchar @capture_instance
, bit @supports_net_changes
, nvarchar @role_name
, nvarchar @index_name
, nvarchar @captured_column_list
, nvarchar @filegroup_name
, bit @allow_partition_switch)

MetaData:

 create procedure sys.sp_cdc_enable_table   
(
@source_schema sysname,
@source_name sysname,
@capture_instance sysname = null,
@supports_net_changes bit = null,
@role_name sysname,
@index_name sysname = null,
@captured_column_list nvarchar(max) = null,
@filegroup_name sysname = null,
@allow_partition_switch bit = 1
)
as
begin
declare @retcode int
,@db_name sysname

-- Verify SQL Server edition
-- CDC is restricted to Eval, Enterprise and Developer editions
IF (sys.fn_MSrepl_editionid () not in (30,31))
BEGIN
DECLARE @edition sysname
SELECT @edition = CONVERT(sysname, SERVERPROPERTY('Edition'))
RAISERROR(22988, 16, -1, @edition)
RETURN (1)
END

-- Verify caller is entitled to enable change data capture for the table
if (isnull(is_srvrolemember('sysadmin'),0) = 0) and (isnull(is_member('db_owner'),0) = 0)
begin
raiserror(22904, 16, -1)
return 1
end

-- Verify database is enabled for change data capture before we switch to cdc
if ([sys].[fn_cdc_is_db_enabled]() != 1)
begin
set @db_name = db_name()
raiserror(22901, 16, -1, @db_name)
return 1
end

-- Switch to database user 'cdc' before executing stored procedure that
-- can cause database DML and DDL triggers to fire.
execute as user = 'cdc'

-- Call internal stored procedure that executes as 'dbo' to do the work.
exec @retcode = sys.sp_cdc_enable_table_internal
@source_schema,
@source_name,
@capture_instance,
@supports_net_changes,
@role_name,
@index_name,
@captured_column_list,
@filegroup_name,
@allow_partition_switch

if (@@error <> 0) or (@retcode <> 0)
begin
revert
return 1
end

revert
return 0
end

No comments:

Post a Comment

Total Pageviews