Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Generate Scripts to Compare Tables

By Absinthe,

I wanted to be able to compare the table data in two databases where I was not able to transfer the data or install a program (such as Red-Gate's SQL Data Compare) but I could use a script. What I found was that there is a stored procedure called sp_table_validation. It is used primarily for handling replication, however, if I can see it I can call it, right? It generates, and compares, row counts and a checksum for a given table.

This particular script goes through all the tables in your current database, and generates the row count/checksum values, and also the call to the stored procedure with them for each table.

The stored procedure is a bit noisy, so I outputted an open comment block before calling it and a closed one when it was completed. This allows you to merely copy the output window and not worry about skipping lines or where to start.

Since I use the SELECT statement to create the script lines, I had to go into TOOLS|OPTIONS|Results in Query Analyzer and change the "Maximum characters per column" to something larger than 256 so I chose 8000.

I ran it on my pubs database. If you would like to compare your pubs database to mine, this is my output, simply USE PUBS and run this:

/*
Generated expected rowcount value of 23 and expected checksum value of -1159171265 for authors.
Generated expected rowcount value of 3 and expected checksum value of 1503689915 for discounts.
Generated expected rowcount value of 43 and expected checksum value of 13472254297 for employee.
Generated expected rowcount value of 14 and expected checksum value of 7349668186 for jobs.
Generated expected rowcount value of 8 and expected checksum value of 1759418 for pub_info.
Generated expected rowcount value of 8 and expected checksum value of -2785219968 for publishers.
Generated expected rowcount value of 86 and expected checksum value of 45716150049 for roysched.
Generated expected rowcount value of 21 and expected checksum value of 16219561870 for sales.
Generated expected rowcount value of 6 and expected checksum value of -2906267733 for stores.
Generated expected rowcount value of 25 and expected checksum value of -21811576433 for titleauthor.
Generated expected rowcount value of 18 and expected checksum value of 9370480104 for titles.

*/
-----------------BEGIN SCRIPT---------------
DECLARE @expected_rowcount bigint
DECLARE @expected_checksum numeric
DECLARE @actual_rowcount bigint
DECLARE @actual_checksum numeric
DECLARE @colList varchar(8000)
DECLARE @comma varchar(1)

DECLARE @RetVal int
SET @RetVal = 0
SET @actual_rowcount = NULL
SET @actual_checksum = NULL
SET @expected_rowcount = NULL
SET @expected_checksum = NULL

CREATE TABLE #tempMismatches
(
Table_Name sysname
,ExpectedRows bigint
,ActualRows bigint
,ExpectedChecksum numeric
,ActualChecksum numeric
)

PRINT 'Table = [authors]'
SET @expected_rowcount = 23
SET @expected_checksum = -1159171265
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [authors],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[au_id],[au_lname],[au_fname],[phone],[address],[city],[state],[zip],[contract]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[authors]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [discounts]'
SET @expected_rowcount = 3
SET @expected_checksum = 1503689915
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [discounts],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[discounttype],[stor_id],[lowqty],[highqty],[discount]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[discounts]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [employee]'
SET @expected_rowcount = 43
SET @expected_checksum = 13472254297
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [employee],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[emp_id],[fname],[minit],[lname],[job_id],[job_lvl],[pub_id],[hire_date]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[employee]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [jobs]'
SET @expected_rowcount = 14
SET @expected_checksum = 7349668186
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [jobs],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[job_id],[job_desc],[min_lvl],[max_lvl]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[jobs]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [pub_info]'
SET @expected_rowcount = 8
SET @expected_checksum = 1759418
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [pub_info],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[pub_id],[logo],[pr_info]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[pub_info]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [publishers]'
SET @expected_rowcount = 8
SET @expected_checksum = -2785219968
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [publishers],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[pub_id],[pub_name],[city],[state],[country]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[publishers]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [roysched]'
SET @expected_rowcount = 86
SET @expected_checksum = 45716150049
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [roysched],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[title_id],[lorange],[hirange],[royalty]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[roysched]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [sales]'
SET @expected_rowcount = 21
SET @expected_checksum = 16219561870
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [sales],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[stor_id],[ord_num],[ord_date],[qty],[payterms],[title_id]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[sales]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [stores]'
SET @expected_rowcount = 6
SET @expected_checksum = -2906267733
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [stores],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[stor_id],[stor_name],[stor_address],[city],[state],[zip]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[stores]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [titleauthor]'
SET @expected_rowcount = 25
SET @expected_checksum = -21811576433
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [titleauthor],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[au_id],[title_id],[au_ord],[royaltyper]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[titleauthor]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

PRINT 'Table = [titles]'
SET @expected_rowcount = 18
SET @expected_checksum = 9370480104
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation [titles],@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '[title_id],[title],[type],[pub_id],[price],[advance],[royalty],[ytd_sales],[notes],[pubdate]'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
INSERT INTO #tempMismatches
SELECT '[titles]', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END

SELECT * FROM #tempMismatches
DROP TABLE #tempMismatches

Total article views: 11340 | Views in the last 30 days: 46
 
Related Articles
FORUM

Expected output

Expected output

FORUM

@@Rowcount is not reliable?

@@rowcount

BLOG

Fix Page Checksum

A checksum value is tagged with every data page in SQL Server. This is used to detect issues on the....

FORUM

Konesans Checksum 2008 issue

Konesans Checksum 2008 issue

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones