April 16, 2012

sp_catalogs_rowset (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_catalogs_rowset(nvarchar @catalog_name)

MetaData:

   
create procedure sys.sp_catalogs_rowset
(
@catalog_name sysname
)
as
select
CATALOG_NAME = name,
DESCRIPTION = convert(nvarchar(1),null)
from
sys.databases
where
name = @catalog_name and (has_dbaccess(name)=1 OR serverproperty('EngineEdition') = 5)
order by 1

sp_bindefault (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_bindefault(nvarchar @defname
, nvarchar @objname
, varchar @futureonly)

MetaData:

 create procedure sys.sp_bindefault    -- - 1996/08/30 20:04  
@defname nvarchar(776), -- name of the default
@objname nvarchar(776), -- table or usertype name
@futureonly varchar(15) = NULL -- flag to indicate extent of binding
as
declare @defid int -- id of the default to bind
declare @futurevalue varchar(15) -- the value of @futureonly that causes
-- the binding to be limited
declare
@vc1 nvarchar(517)
,@tab_id int
,@col_id int
,@parent_obj int
,@colname sysname
,@xtype tinyint
,@xusertype int
,@xtypelen int
,@cur_default int
,@schid int

declare
@UnqualDef sysname
,@QualDef1 sysname
,@QualDef2 sysname
,@QualDef3 sysname
,@UnqualObj sysname
,@QualObj1 sysname
,@QualObj2 sysname
,@QualObj3 sysname

set cursor_close_on_commit off
set nocount on

select @futurevalue = 'futureonly' -- initialize @futurevalue

-- When a default or rule is bound to a user-defined datatype, it is also
-- bound, by default, to any columns of the user datatype that are currently
-- using the existing default or rule as their default or rule. This default
-- action may be overridden by setting @futureonly = @futurevalue when the
-- procedure is invoked. In this case existing columns with the user
-- datatype won't have their existing default or rule changed.

-- get name parts --
select @UnqualDef = parsename(@defname, 1),
@QualDef1 = parsename(@defname, 2),
@QualDef2 = parsename(@defname, 3),
@QualDef3 = parsename(@defname, 4)

select @UnqualObj = parsename(@objname, 1),
@QualObj1 = parsename(@objname, 2),
@QualObj2 = parsename(@objname, 3),
@QualObj3 = parsename(@objname, 4)

if (@UnqualDef is null OR @QualDef3 is not null)
begin
raiserror(15253,-1,-1,@defname)
return (1)
end

if (@UnqualObj is null OR @QualObj3 is not null)
begin
raiserror(15253,-1,-1,@objname)
return (1)
end

-- -- -- -- -- -- -- -- -- Verify database.
if ((@QualObj2 is not null and @QualObj1 is null)
or (@QualDef2 is not null and @QualDef2 <> db_name()))
begin
raiserror(15076,-1,-1)
return (1)
end

-- Check that the @futureonly argument, if supplied, is correct.
if (@futureonly is not null)
begin
select @futureonly = lower(@futureonly)
if (@futureonly <> @futurevalue)
begin
raiserror(15100,-1,-1)
return (1)
end
end

BEGIN TRANSACTION

-- Check to see that the default exists and get its id.
select @defid = object_id, @parent_obj = parent_object_id from sys.objects
where object_id = object_id(@defname, 'local')
and type='D ' -- default object 6

-- Share lock default so it cannot be dropped
if not (@defid is null)
begin
EXEC %%Object(MultiName = @defname).LockMatchID(ID = @defid, Exclusive = 0, BindInternal = 0)
if @@error <> 0
select @defid = null
end

if @defid is null
begin
raiserror(15016,-1,-1,@UnqualDef)
goto error_abort_exit
end

if @parent_obj > 0
begin
raiserror(15050,-1,-1,@defname)
goto error_abort_exit
end

-- Try to resolve column first. We need to extract
-- and verify the table and column names and make sure the user owns
-- the table that is getting the default bound. We also need to ensure
-- that we don't overwrite any DRI style defaults.
if @QualObj1 is not null
begin
if (@QualObj2 is not null)
select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
else
select @vc1 = QuoteName(@QualObj1)

-- Check that table and column exist
select @tab_id = o.object_id
from sys.tables o join sys.columns c
on c.object_id = o.object_id
where o.object_id = object_id(@vc1,'local')
and c.name = @UnqualObj

if @tab_id is not null
begin
declare @is_sparse int
declare @is_column_set int

-- Since binding a default is a schema change, update schema count
-- for the object in the sysobjects table.
EXEC %%Object(MultiName = @vc1).LockMatchID(ID = @tab_id, Exclusive = 1, BindInternal = 0)

-- Check again that table and column exist
if @@error = 0
select @xtype = system_type_id,
@xtypelen = max_length,
@cur_default = default_object_id,
@is_sparse = is_sparse,
@is_column_set = is_column_set
from sys.columns
where object_id = @tab_id
and name = @UnqualObj
if @xtype is null
begin
raiserror(15148,-1,-1, @objname)
goto error_abort_exit
end

-- If the column type is timestamp, varchar(max), nvarchar(max), varbinary(max), disallow the bind.
-- If the column is computed, disallow the bind.
if ( type_name(@xtype) in ('timestamp', 'xml')
or ( type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary')
and @xtypelen = -1 )
or ColumnProperty(@tab_id, @UnqualObj, 'IsComputed') = 1
or @is_sparse = 1
or @is_column_set = 1
or @xtype = 240 ) -- CLR UDT
begin
raiserror(15101,-1,-1)
goto error_abort_exit
end

-- If the column category is identity, disallow the bind.
-- Defaults can't be bound to identity columns.
if 1 = ColumnProperty(@tab_id, @UnqualObj, 'IsIdentity')
begin
raiserror(15102,-1,-1)
goto error_abort_exit
end

-- Check to see if the column was created with or altered
-- to have a DRI style default value.
if (@cur_default is not null) and exists
(select *
from sys.objects o
where @cur_default = o.object_id
and @tab_id = o.parent_object_id)
begin
raiserror(15103,-1,-1)
goto error_abort_exit
end

EXEC %%ColumnEx(ObjectID = @tab_id, Name = @UnqualObj).SetDefault(ID = @defid)

-- EMDEventType(x_eet_Bind_Default), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name
-- EMDUniversalClass(x_eunc_Table), target major id, 1 means target name is column, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 218, ID = 1, ID = @defid, ID = 0, Value = NULL,
ID = 1, ID = @tab_id, ID = 1, Value = @UnqualObj, ID = 3,
Value = @defname, Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
raiserror(15511,-1,-1)
end
end

-- We're binding to a user type. In this case, the @objname
-- is really the name of the user datatype.
-- When we bind to a user type, any existing columns get changed
-- to the new binding unless their current binding is not equal
-- to the current binding for the usertype or if they set the
-- @futureonly parameter to @futurevalue.
if @tab_id is null
begin

-- Get the current default for the datatype.
if @QualObj2 is null
select @xusertype = user_type_id, @cur_default = default_object_id,
@xtype = system_type_id,
@xtypelen = max_length,
@schid = schema_id
from sys.types
where user_type_id = type_id(@objname)
and is_table_type=0

-- Ex-lock and check permission
if not (@xusertype is null)
begin
EXEC %%ScalarType(MultiName = @objname).LockMatchID(ID = @xusertype, Exclusive = 1)
if (@@error <> 0)
select @xusertype = null
end

if @xusertype is null
begin
raiserror(15148,-1,-1, @objname)
goto error_abort_exit
end

if ((type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary') and
@xtypelen = -1)
or type_name(@xtype) = 'xml'
or @xtype = 240 ) -- CLR UDT
begin
raiserror(15101,-1,-1)
goto error_abort_exit
end

if exists (select * from sys.columns c where user_type_id = @xusertype and c.is_sparse = 1)
begin
raiserror(33079,-1,-1, @objname)
goto error_abort_exit
end

EXEC %%ScalarType(ID = @xusertype).SetDefault(ID = @defid)
raiserror(15512,-1,-1)

-- need the new binding.
if isnull(@futureonly, ' ') <> @futurevalue
begin

declare @cur_tab_id int
,@bad_tab_id int

select @cur_tab_id = 0 -- detect table id change for lock schema
,@bad_tab_id = 0 -- skip bad tables (dropped, etc)

declare ms_crs_t1 cursor local static for
select distinct
c.object_id, c.column_id
from sys.columns c join sys.tables o
on c.object_id = o.object_id
where c.user_type_id = @xusertype
and ((c.default_object_id = 0)
or (c.default_object_id = @cur_default))
order by c.object_id
for read only

open ms_crs_t1
fetch next from ms_crs_t1 into
@tab_id, @col_id

while @@fetch_status = 0
begin
if @cur_tab_id <> @tab_id -- not same table
begin
select @cur_tab_id = @tab_id
select @vc1 = quotename(schema_name(OBJECTPROPERTY(@tab_id,'SchemaId'))) + '.'
+ quotename(object_name(@tab_id))

EXEC %%Object(MultiName = @vc1).LockMatchID(ID = @tab_id, Exclusive = 1, BindInternal = 0)
if @@error <> 0 -- bad table, eg. removed
select @bad_tab_id = @tab_id
end

if @bad_tab_id <> @tab_id -- table schema locked
begin
-- Column cannot be dropped due to type shared lock
select @colname = COL_NAME(@tab_id, @col_id)
EXEC %%ColumnEx(ObjectID = @tab_id, Name = @colname).SetDefault(ID = @defid)
end

fetch next from ms_crs_t1 into
@tab_id, @col_id

end

deallocate ms_crs_t1
raiserror(15513,-1,-1)
end

-- EMDEventType(x_eet_Bind_Default), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name
-- EMDUniversalClass(x_eunc_Type), target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 218, ID = 1, ID = @defid, ID = 0, Value = NULL,
ID = 6, ID = @xusertype, ID = 0, Value = NULL, ID = 3,
Value = @defname, Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end

-- SUCCESS --
COMMIT TRANSACTION
return (0)

error_abort_exit:
COMMIT TRANSACTION
return 1 -- sp_bindefault

sp_cdc_help_change_data_capture (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_help_change_data_capture(nvarchar @source_schema
, nvarchar @source_name)

MetaData:

   
create procedure [sys].[sp_cdc_help_change_data_capture]
(
@source_schema sysname = null,
@source_name sysname = null
)
as
begin
set nocount on

declare @retcode int,
@capture_cnt int

-- Verify database is currently enabled for change data capture
if ([sys].[fn_cdc_is_db_enabled]() != 1)
begin
declare @db_name sysname
set @db_name = db_name()
raiserror(22901, 16, -1, @db_name)
return 1
end

create table #capture_instances
(
capture_instance sysname collate database_default null,
object_id int null
)

set @source_schema = rtrim(@source_schema)
set @source_name = rtrim(@source_name)

-- Get a list of potential capture instances.
exec @retcode = sys.sp_cdc_get_capture_instances @source_schema, @source_name

if @retcode <> 0
begin
return 1
end

-- Eliminate from the list any entries that the caller is not
-- authorized to access
delete from #capture_instances
where sys.fn_cdc_has_select_access(capture_instance) = 0

-- If there are no elements in the #capture_instances and
-- an explicit schema was entered, return error
select @capture_cnt = count(*) from #capture_instances

if (@capture_cnt = 0) and (@source_schema is not null)
begin
raiserror(22981, 16, -1)
return 1
end

-- Return information on capture instances
exec sys.sp_cdc_change_data_capture

return 0
end

sp_cdc_disable_db (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_disable_db()

MetaData:

 create procedure [sys].[sp_cdc_disable_db]  
as
begin
declare @retcode int
,@db_name sysname

-- Verify caller is authorized to disable change data capture for the database
if (isnull(is_srvrolemember('sysadmin'),0) = 0)
begin
raiserror(22902, 16, -1)
return 1
end

-- Verify database is currently enabled for change data capture
if ([sys].[fn_cdc_is_db_enabled]() != 1)
begin
set @db_name = db_name()
raiserror(22901, 10, -1, @db_name)
return 0
end

exec @retcode = sys.sp_cdc_disable_db_internal

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

return 0
end

sp_cdc_disable_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_disable_table(nvarchar @source_schema
, nvarchar @source_name
, nvarchar @capture_instance)

MetaData:

 create procedure sys.sp_cdc_disable_table   
(
@source_schema sysname,
@source_name sysname,
@capture_instance sysname
)
as
begin
declare @retcode int
,@db_name sysname

-- Verify caller is authorized to disable change data capture for the table.
-- Caller must either be a member of the fixed sysadmin SQL Server role, or
-- a member of the current database db_owner role.
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 switching to the database 'cdc' user
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 the database 'cdc' user prior to calling module that can
-- cause DML and/or DDL triggers to fire.
execute as user = 'cdc'

-- Call internal stored procedure that executes as 'cdc' user to do the work
exec @retcode = sys.sp_cdc_disable_table_internal
@source_schema,
@source_name,
@capture_instance

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

revert
return 0
end

sp_cdc_restoredb (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_restoredb(nvarchar @srv_orig
, nvarchar @db_orig
, int @keep_cdc)

MetaData:

 create procedure [sys].[sp_cdc_restoredb]  
(
@srv_orig sysname,
@db_orig sysname,
@keep_cdc int
)
as
begin
set nocount on

declare @retcode int

--
-- Security Check: RESTORE statement requires sysadmin, dbcreator, or
-- dbo (THE dbo, not just any member of the db_owner
-- role.)
--
if (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
and (ISNULL(IS_SRVROLEMEMBER('dbcreator'),0) = 0)
and (ISNULL(IS_MEMBER('db_owner'),0) = 0)
begin
raiserror(18799, 16, -1)
return 1
end

exec @retcode = sys.sp_cdc_restoredb_internal @srv_orig
, @db_orig
, @keep_cdc

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

return 0
end

sp_cdc_help_jobs (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_help_jobs()

MetaData:

 create procedure sys.sp_cdc_help_jobs  
as
begin

declare @retcode int

--
-- Job security check proc
--
exec @retcode = [sys].[sp_MScdc_job_security_check]
if @retcode <> 0 or @@error <> 0
return(1)

-- Verify database is currently enabled for change data capture
if ([sys].[fn_cdc_is_db_enabled]() != 1)
begin
declare @db_name sysname
set @db_name = db_name()
raiserror(22901, 16, -1, @db_name)
return 1
end

exec @retcode = sys.sp_cdc_help_jobs_internal

if @@error <> 0 or @retcode <> 0
return 1

return 0
end

sp_cdc_get_ddl_history (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_get_ddl_history(nvarchar @capture_instance)

MetaData:

 create procedure [sys].[sp_cdc_get_ddl_history]                    
(
@capture_instance sysname
)
as
begin

-- Verify database is currently enabled for change data capture
if ([sys].[fn_cdc_is_db_enabled]() != 1)
begin
declare @db_name sysname
set @db_name = db_name()
raiserror(22901, 16, -1, @db_name)
return 1
end

if (@capture_instance is null) or (rtrim(@capture_instance) = N'')
begin
raiserror(22963, 16, -1, N'@capture_instance')
return 1
end

if ([sys].[fn_cdc_has_select_access](rtrim(@capture_instance)) = 0)
begin
raiserror(22981, 16, -1)
return 1
end

exec sys.sp_cdc_ddl_history @capture_instance

return 0
end

sp_cdc_get_captured_columns (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_get_captured_columns(nvarchar @capture_instance)

MetaData:

   
create procedure [sys].[sp_cdc_get_captured_columns]
(
@capture_instance sysname
)
as
begin

-- Verify database is currently enabled for change data capture
if ([sys].[fn_cdc_is_db_enabled]() != 1)
begin
declare @db_name sysname
set @db_name = db_name()
raiserror(22901, 16, -1, @db_name)
return 1
end

set @capture_instance = rtrim(@capture_instance)

if (@capture_instance is null) or (@capture_instance = N'')
begin
raiserror(22963, 16, -1, N'@capture_instance')
return 1
end

if ([sys].[fn_cdc_has_select_access](rtrim(@capture_instance)) = 0)
begin
raiserror(22981, 16, -1)
return 1
end

exec sys.sp_cdc_captured_columns @capture_instance

return 0
end

sp_cdc_generate_wrapper_function (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_generate_wrapper_function(nvarchar @capture_instance
, bit @closed_high_end_point
, nvarchar @column_list
, nvarchar @update_flag_list)

MetaData:

 create procedure [sys].[sp_cdc_generate_wrapper_function]                    
(
@capture_instance sysname = null,
@closed_high_end_point bit = 1,
@column_list nvarchar(max) = null,
@update_flag_list nvarchar(max) = null
)
as
begin
set nocount on

declare @retcode int,
@capture_cnt int,
@object_id int,
@quoted_change_table nvarchar(260),
@db_name sysname

set @quoted_change_table = N'[cdc].' + quotename(@capture_instance + N'_CT')
set @db_name = db_name()

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

create table #capture_instances
(
capture_instance sysname collate database_default null,
object_id int null
)

create table #create_scripts
(
function_name nvarchar(145) collate database_default null,
create_script nvarchar(max) collate database_default null
)

if (@capture_instance is null) or (rtrim(@capture_instance) = N'')
begin
-- If the capture instance is not specified, the @column_list and
-- @update_flag_list parameters must both be null
if (@column_list is not null) or (@update_flag_list is not null)
begin
raiserror(22921, 16, -1)
return 1
end

-- Get a list of potential capture instances.
exec @retcode = sys.sp_cdc_get_capture_instances null, null

if @retcode <> 0
begin
return 1
end
end
else
begin
-- Get a list of potential capture instances.
exec @retcode = sys.sp_cdc_get_capture_instances null, null

if @retcode <> 0
begin
return 1
end

-- If the capture instance is specified, it must exist
if not exists (
select capture_instance
from #capture_instances
where capture_instance = @capture_instance
)
begin
raiserror(22920, 16, -1, @capture_instance, @db_name)
return 1
end

-- Remove all entries except the one for the named instance
delete from #capture_instances
where capture_instance <> @capture_instance
end

if (@closed_high_end_point is null)
begin
set @closed_high_end_point = 1
end

-- Eliminate from the list any entries that the caller is not
-- authorized to access
delete from #capture_instances
where sys.fn_cdc_has_select_access(capture_instance) = 0

-- If there are no elements in #capture_instances and
-- an explicit capture instance was entered, return error
select @capture_cnt = count(*) from #capture_instances

if (@capture_cnt = 0) and (@capture_instance is not null)
begin
raiserror(22981, 16, -1)
return 1
end

-- Generate wrapper for the capture instances
exec sys.sp_cdc_generate_wrapper_function_internal
@closed_high_end_point
,@column_list
,@update_flag_list

select function_name, create_script from #create_scripts

return 0
end

sp_cdc_dbsnapshotLSN (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_dbsnapshotLSN(nvarchar @db_snapshot)

MetaData:

   
create procedure [sys].[sp_cdc_dbsnapshotLSN]
(
@db_snapshot sysname,
@lastLSN binary(10) = null output,
@lastLSNstr varchar(40) = null output
)
as
begin
declare @command nvarchar(max), @str nvarchar(1000), @begin_char int
,@lsn binary(10), @lsn_str nvarchar(40), @db_snapshot_quoted nvarchar(1000)

declare @temp table(parentObject nvarchar(255),Object nvarchar(255),field nvarchar(255),value nvarchar(255))

-- Verify that the database snapshot exists
if not exists (
select name from sys.databases
where name = @db_snapshot
and source_database_id is not null)
begin
select @db_snapshot_quoted = quotename(@db_snapshot)
raiserror(22862, 16, -1, @db_snapshot_quoted)
return 1
end

set @lsn = 0x00
set @lsn_str = null
set @command = N'dbcc dbtable(' + quotename(@db_snapshot) + N') with tableresults, no_infomsgs'

insert into @temp exec( @command )
select @str = value from @temp where field = N'm_splitPoint';

if @str is not null
begin
select @begin_char = CHARINDEX('(', @str)
select @lsn_str = SUBSTRING(@str, @begin_char + 3, 8) + SUBSTRING(@str, @begin_char + 12, 8)
+ SUBSTRING(@str, @begin_char + 21, 4)
select @lsn = sys.fn_cdc_hexstrtobin(@lsn_str)
end

set @lastLSN = @lsn
set @lastLSNstr = @lsn_str

return 0
end

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

sp_cdc_enable_db (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_db()

MetaData:

 create procedure [sys].[sp_cdc_enable_db]  
as
begin
declare @retcode int
,@containment tinyint
,@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 authorized to enable change data capture for the database
if (isnull(is_srvrolemember('sysadmin'),0) = 0)
begin
raiserror(22902, 16, -1)
return 1
end

--
-- Contained Database check (Replication is not yet supported on contained databases)
-- If the current database is a contained database, then we error out.
--
SELECT @containment=containment FROM sys.databases WHERE
database_id = db_id()
if (@containment != 0)
BEGIN
set @db_name = db_name()
RAISERROR(12839, 16, -1, @db_name)
RETURN(1)
END

exec @retcode = sys.sp_cdc_enable_db_internal

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

return 0
end

sp_cdc_drop_job (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_drop_job(nvarchar @job_type)

MetaData:

 create procedure sys.sp_cdc_drop_job  
(
@job_type nvarchar(20)
)
as
begin
set nocount on

declare @retval int
--
-- Authorization check.
--
if (isnull(is_srvrolemember('sysadmin'),0) = 0) and (isnull(is_member('db_owner'),0) = 0)
begin
raiserror(22904, 16, -1)
return(1)
end

-- NOTE: The bit identifying a database as enabled for cdc is
-- cleared before the jobs can be dropped, so only admin
-- authorization is checked here. If this changes, then
-- job security can be checked.
--
-- CDC Job security check
--
-- exec @retcode = [sys].[sp_MScdc_job_security_check]
-- if @retcode <> 0 or @@error <> 0
-- return (1)

set @job_type = rtrim(ltrim(lower(@job_type)))

-- Verify parameter
if ((@job_type is null) or (@job_type not in (N'capture', N'cleanup')))
begin
raiserror(22992, 16, -1, @job_type)
return(1)
end

-- Call internal stored procedure to drop the job
exec @retval = sys.sp_cdc_drop_job_internal
@job_type

if @@error <> 0 or @retval <> 0
return 1

return(0)
end

sp_cdc_cleanup_change_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_cleanup_change_table(nvarchar @capture_instance
, binary @low_water_mark
, bigint @threshold)

MetaData:

 create procedure [sys].[sp_cdc_cleanup_change_table]  
(
@capture_instance sysname,
@low_water_mark binary(10),
@threshold bigint = 5000
)
as
begin
declare @retcode int
,@db_name sysname
,@xstr1 nvarchar(22)

set nocount on

set @db_name = db_name()

-- Verify caller is authorized to clean up change tracking
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 tracking
if ([sys].[fn_cdc_is_db_enabled]() != 1)
begin
raiserror(22910, 16, -1, @db_name)
return 1
end

-- Parameter @threshold must be positive
if (@threshold <= 0)
begin
raiserror(22850, 16, -1)
return 1
end

-- If non-null, parameter @low_water_mark must appear as the start_lsn
-- value of a current entry in the cdc.lsn_time_mapping table.
if (@low_water_mark is not null)
begin
if not exists
( select start_lsn from cdc.lsn_time_mapping
where start_lsn = @low_water_mark )
begin
set @xstr1 = upper(sys.fn_varbintohexstr(@low_water_mark))
raiserror(22964, 16, -1, @xstr1)
return 1
end
end

-- Call internal stored procedure to do the work
-- Switch to database 'cdc' user to mitigate against malicious dbo triggers
execute as user = 'cdc'

exec @retcode = sys.sp_cdc_cleanup_change_table_internal
@capture_instance,
@low_water_mark,
@threshold

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

revert

return 0
end

sp_cdc_change_job (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_change_job(nvarchar @job_type
, int @maxtrans
, int @maxscans
, bit @continuous
, bigint @pollinginterval
, bigint @retention
, bigint @threshold)

MetaData:

 create procedure sys.sp_cdc_change_job  
(
@job_type nvarchar(20) = N'capture',
@maxtrans int = null,
@maxscans int = null,
@continuous bit = null,
@pollinginterval bigint = null,
@retention bigint = null,
@threshold bigint = null
)
as
begin
set nocount on

declare @retcode int
,@db_name sysname

set @db_name = db_name()

--
-- Job security check proc
--
exec @retcode = [sys].[sp_MScdc_job_security_check]
if @retcode <> 0 or @@error <> 0
return(1)

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

set @job_type = rtrim(ltrim(lower(@job_type)))

-- Verify parameter
if @job_type not in (N'capture', N'cleanup')
begin
raiserror(22992, 16, -1, @job_type)
return(1)
end

-- Call internal stored procedure to complete verification and update job attributes
exec @retcode = sys.sp_cdc_change_job_internal
@job_type,
@maxtrans,
@maxscans,
@continuous,
@pollinginterval,
@retention,
@threshold

if @@error <> 0 or @retcode <> 0
return 1

return 0
end

sp_cdc_add_job (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_add_job(nvarchar @job_type
, bit @start_job
, int @maxtrans
, int @maxscans
, bit @continuous
, bigint @pollinginterval
, bigint @retention
, bigint @threshold
, bit @check_for_logreader)

MetaData:

 create procedure sys.sp_cdc_add_job  
(
@job_type nvarchar(20),
@start_job bit = 1,
@maxtrans int = null,
@maxscans int = null,
@continuous bit = null,
@pollinginterval bigint = null,
@retention bigint = null,
@threshold bigint = null,
@check_for_logreader bit = 0
)
as
begin
set nocount on

declare @job_name sysname
,@retval int
,@job_id uniqueidentifier
,@old_job_id uniqueidentifier
,@job_step_uid uniqueidentifier
,@index int
,@category_name sysname
,@command nvarchar(1000)
,@server sysname
,@databasename sysname
,@user sysname
,@schedule_name sysname
,@database_id int
,@valid_job bit
,@description nvarchar(100)
,@step_name nvarchar(100)
,@logreader_exists bit

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

--
-- CDC Job security check
--
exec @retval = [sys].[sp_MScdc_job_security_check]
if @retval <> 0 or @@error <> 0
return (1)

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

set @job_type = rtrim(ltrim(lower(@job_type)))

--
-- Parameter validation
--

-- job type must be either 1 or 2
if ((@job_type is null) or (@job_type not in (N'capture', N'cleanup')))
begin
raiserror(22992, 16, -1, @job_type)
return(1)
end

--
-- Insure that transactional replication is not also enabled for the database
--
if (@job_type = N'capture')
begin
set @logreader_exists = 0
exec @retval = [sys].[sp_MScdc_tranrepl_check] @logreader_exists = @logreader_exists output, @skip_remote_check = @check_for_logreader
if @retval <> 0 or @@error <> 0
return (1)
end
-- coming from sp_cdc_enable_table, when db is published for tran and there is logreader agent already, don't raiserror
if (@logreader_exists = 1) and (@check_for_logreader = 1)
return 0

-- Set job specific defaults
if (@job_type = N'capture')
begin
if (@continuous is null)
set @continuous = 1

if (@maxtrans is null)
set @maxtrans = 500

if (@maxscans is null)
set @maxscans = 10

if (@pollinginterval is null)
begin
if (@continuous = 1)
set @pollinginterval = 5
else
set @pollinginterval = 0
end
end
else
begin
if (@retention is null)
set @retention = 4320
if (@threshold is null)
set @threshold = 5000
end

-- Only @retention, @threshold, and @start_job may have non-null values for cleanup job
if (@job_type = N'cleanup') and
( (@pollinginterval is not null) or
(@maxtrans is not null) or
(@continuous is not null) or
(@maxscans is not null))
begin
raiserror(22996, 16, -1)
return(1)
end

-- Only @pollinginterval, @maxtrans, @maxscans, @continuous, and @start_job may have non-null values for capture job
if (@job_type = N'capture') and ((@retention is not null) or (@threshold is not null))
begin
raiserror(22995, 16, -1)
return(1)
end

if (@job_type = N'capture')
select @retention = 0, @threshold = 0
else
select @pollinginterval = 0, @maxtrans = 0, @continuous = 0, @maxscans = 0

-- 24 hour maximum on polling interval
if (@pollinginterval > (60*60*24)) or (@pollinginterval < 0)
begin
raiserror(22990, 16, -1)
return(1)
end

-- Retention may not be negative or greater than 52594800 if adding cleanup job
if (@job_type = N'cleanup') and ((@retention <= 0) or (@retention > 52594800))
begin
raiserror(22994, 16, -1)
return(1)
end

-- Threshold may not be negative if adding cleanup job
if (@job_type = N'cleanup') and (@threshold <= 0)
begin
raiserror(22850, 16, -1)
return(1)
end

-- maxtrans must be greater than 0
if (@job_type = N'capture') and (@maxtrans <= 0)
begin
raiserror(22991, 16, -1)
return(1)
end

-- maxscans must be greater than 0
if (@job_type = N'capture') and (@maxscans <= 0)
begin
raiserror(22970, 16, -1)
return(1)
end

-- Call internal stored procedure to create the job
exec @retval = sys.sp_cdc_add_job_internal
@job_type,
@start_job,
@maxtrans,
@maxscans,
@continuous,
@pollinginterval,
@retention,
@threshold

if @retval <> 0 or @@error <> 0
return (1)

return 0
end

Total Pageviews