June 11, 2012

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


sys.sp_table_validation(nvarchar @table
, smallint @rowcount_only
, nvarchar @owner
, tinyint @full_or_fast
, bit @shutdown_agent
, nvarchar @table_name
, nvarchar @column_list)


 create procedure sys.sp_table_validation  
@table sysname, -- table name or sync object name
@expected_rowcount bigint = NULL OUTPUT,
@expected_checksum numeric = NULL OUTPUT,
@rowcount_only smallint = 1,
The @rowcount_only param is overloaded for shiloh release due to backward compatibility concerns.
In shiloh, the checksum functionality has changed. So 7.0 subscribers will have the old checksum
routines, which generate different CRC values, and do not have functionality for vertical partitions,
or logical table structures where column offsets differ (due to ALTER TABLEs that DROP and ADD columns).

In 7.0, this was a bit column. 0 meant do not do just a rowcount - do a checksum. 1 meant just do a

For Shiloh, this parameter is changed to a smallint. The name "rowcount_only" is now a
misnomer given the overloaded functionality. It is really a "type of check requested"
parameter. But, the old name is retained for backward compatibility. It can take
these values:

0 - Do a 7.0 compatible checksum
1 - Do a rowcount check only (remains the default)
2 - Use new Shiloh checksum functionality.

Note that because 7.0 subscribers will
take this parameter as a bit type, not a smallint, it will be interpreted as simply
ON. That means that passing a 2, and having a 7.0 subscriber, will result in the 7.0
subscriber doing only rowcount validation. The Shiloh subscribers will do both
rowcount and checksum. If you want 7.0 subscribers to do checksum validation, use
the value of 0 for this parameter. Shiloh subscribers can do the 7.0 compatible
checksum, but that checksum has the same 7.0 limitations for vertical partitions
and differences in physical table structure.)


@owner sysname = NULL,
@full_or_fast tinyint = 2, -- full (value 0) does COUNT(*)
-- fast (value 1) uses sysindexes.rowcnt if table (not view);
-- conditional fast (VALUE 2) , first tries fast method, but
-- reverts to full if fast method shows differences.
@shutdown_agent bit = 0, -- If 1 will raise error 20578, which will signal replication agent to shutdown
@table_name sysname = NULL, -- table name of sync object or the table name for output message
@column_list nvarchar(max) = NULL -- the list of columns - that way the user can skip computed columns etc
declare @retcode int
exec @retcode = sys.sp_MStable_validation
@table = @table,
@expected_rowcount = @expected_rowcount output,
@expected_checksum = @expected_checksum output,
@rowcount_only = @rowcount_only,
@owner = @owner,
@full_or_fast = @full_or_fast,
@shutdown_agent = @shutdown_agent,
@table_name = @table_name,
@column_list = @column_list
return @retcode

No comments:

Post a Comment

Total Pageviews