Technical Article

Generate Scripts to Compare Tables

,

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

SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

PRINT '/*'

CREATE TABLE #temp
(
 table_name sysname
 ,rowcountval bigint
 ,checksumval numeric
 ,colList varchar(4000)
)

DECLARE @actual_rowcount bigint
DECLARE @actual_checksum numeric
DECLARE @colList varchar(4000)
DECLARE @comma varchar(1)
DECLARE @table_name sysname
DECLARE myCursor CURSOR FOR 
 SELECT TABLE_NAME
 FROM INFORMATION_SCHEMA.tables 
 WHERE TABLE_TYPE = 'BASE TABLE'
OPEN myCursor

FETCH NEXT FROM myCursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @comma = ''
 SET @colList = ''
 SET @actual_rowcount = NULL
 SET @actual_checksum = NULL

 SELECT @colList = @colList + @comma + '['+column_name+']', @comma=',' 
 FROM information_schema.columns 
 WHERE table_name = @table_name
 ORDER BY ORDINAL_POSITION

 EXEC sp_table_validation @table_name
 ,@actual_rowcount OUTPUT
 ,@actual_checksum OUTPUT
 ,2
 ,NULL
 ,0
 ,@column_list = @colList
 
 INSERT INTO #temp (table_name, rowcountval, checksumval, colList) 
 VALUES (@table_name,@actual_rowcount,@actual_checksum,@colList) 

 FETCH NEXT FROM myCursor INTO @table_name
END

CLOSE myCursor
DEALLOCATE myCursor

PRINT ''
PRINT '*/'
PRINT '-----------------BEGIN SCRIPT---------------'
PRINT 'DECLARE @expected_rowcount bigint'
PRINT 'DECLARE @expected_checksum numeric'
PRINT 'DECLARE @actual_rowcount bigint'
PRINT 'DECLARE @actual_checksum numeric'
PRINT 'DECLARE @colList varchar(8000)'
PRINT 'DECLARE @comma varchar(1)'
PRINT '' 
PRINT 'DECLARE @RetVal int'
PRINT 'SET @RetVal = 0'
PRINT 'SET @actual_rowcount = NULL'
PRINT 'SET @actual_checksum = NULL'
PRINT 'SET @expected_rowcount = NULL'
PRINT 'SET @expected_checksum = NULL'
PRINT ''
PRINT 'CREATE TABLE #tempMismatches'
PRINT '('
PRINT ' Table_Name sysname'
PRINT ' ,ExpectedRows bigint'
PRINT ' ,ActualRows bigint'
PRINT ' ,ExpectedChecksum numeric'
PRINT ' ,ActualChecksum numeric'
PRINT ')'

SELECT
"
PRINT 'Table = "+QUOTENAME(table_name)+"'
SET @expected_rowcount = "+ ISNULL(CONVERT(varchar,rowcountval),'NULL') + "
SET @expected_checksum = "+ ISNULL(CONVERT(varchar,CheckSumVal),'NULL') + "
SET @actual_rowcount = NULL
SET @actual_checksum = NULL

EXEC @RetVal = sp_table_validation "+ QUOTENAME(table_name) +",@actual_rowcount OUTPUT,@actual_checksum OUTPUT,2,NULL,0,@column_list = '"+ colList +"'
IF @expected_rowcount <> @actual_rowcount OR @expected_checksum <> @actual_checksum
BEGIN
 INSERT INTO #tempMismatches
 SELECT '"+ QUOTENAME(table_name) +"', @expected_rowcount, @actual_rowcount, @expected_checksum, @actual_checksum
END
"
FROM #temp

DROP TABLE #temp

PRINT 'SELECT * FROM #tempMismatches'
PRINT 'DROP TABLE #tempMismatches'

SET NOCOUNT OFF
SET QUOTED_IDENTIFIER ON

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.67 (3)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.67 (3)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

Undocumented T-SQL Tricks With Logs

An interesting feature added to SQL Server 7.0 and 2000 is the ability to purge
an error log through a stored procedure or DBCC command. In this article, Brian Knight shows some of the undocumented stored procedures to detect, read and purge an SQL Server error log in T-SQL.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1 (1)

You rated this post out of 5. Change rating

2001-06-27

14,816 reads