Efficiently Compare Two SQL Server Tables Using Mostly VBScript

Recently, a friend of mine asked to write a script to compare two SQL Server tables using mostly VBScript. The very thought made me shudder; I wanted desperately to compare the tables in SQL Server but ultimately, the company’s requirements prevented us from doing so. Here were the stipulations:

  • First, the script should be as resuable as possible. Many tables will be compared.
  • The column names are unknown — only the database name and table were consistent.
  • The SQL Servers were 2005 and both located on the same domain.
  • Windows Authentication must be used.
  • SSIS/DTS cannot be used.
  • Files can be written to the hard drive, but only as a last resort.
  • OPENROWSET/OPENDATASOURCE/Linked Servers cannot be used.
  • The script’s output must be either TRUE or FALSE. TRUE if the tables matched, FALSE if they didn’t.
  • IDENTITY columns must be ignored.

I told him I’d be back in 20 minutes as my brain figured it out. This is what it came up with:

  • ADO’s recordset getstring() will be used for direct comparisons. No variables will need to be assigned, no data will need to be written.
  • The INFORMATION_SCHEMA will be queried to discover column names. Column names will be reused by both SELECT and ORDER BY.
  • ORDER BY will be used to ensure the data is returned in the same order on both tables.
  • COLUMNPROPERTY will be used to check if the column is an identity (I know excluding the ID means that the table may not be exactly a match, but it’s what was requested, so I obliged.)

So without further ADO ;)

strServer1 = "server1"
strServer2 = "server2"
strDatabaseName = "mydatabase"
strTableName = "mytable"

CALL CompareTables(strServer1,strServer2,strDatabaseName,strTableName)

SUB CompareTables(server1,server2,databaseName,tableName)

'This is a SQL Server 2005 Connection String with Windows Authentication. Change as necessary.
connString1 = "Provider=SQLNCLI;Server=" & server1 & ";Database=" & databaseName & ";Integrated Security=SSPI;"
connString2 = "Provider=SQLNCLI;Server=" & server2 & ";Database=" & databaseName & ";Integrated Security=SSPI;"

'Since this is a template script, we don't know what the columns are so we'll use the INFORMATION_SCHEMA to figure it out.
'Not only will this be useful in the SELECT statement (SELECT col1, col2, col3) but also is necessary for the ORDER BY.
'Because we'll be using a text comparison, columns and order by must be  in the same exact order on both server1 and server2
strSQLColumns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & tableName & "' AND (COLUMNPROPERTY(OBJECT_ID('" & tableName & "'),column_name,'IsIdentity')) != 1 ORDER BY ORDINAL_POSITION ASC"
SET rsColumns = CreateObject("adodb.recordset")
rsColumns.Open strSQLColumns, connString1, 1,1
IF rsColumns.eof and rsColumns.bof THEN
Msgbox "The table has no columns or does not exist."
rsColumns.close
SET rsColumns = NOTHING
Wscript.quit
ELSE
do until rsColumns.eof
strColumns = strColumns & rsColumns("COLUMN_NAME") & ","
rsColumns.movenext
loop
'Take off last comma
strColumns = left(strColumns,len(strColumns)-1)
rsColumns.close
SET rsColumns = NOTHING
END IF

'Ninja
strSQL = "SELECT " & strColumns & " from " & tableName & " ORDER BY " & strColumns & " asc"

SET rsserver1 = CreateObject("adodb.recordset")
rsserver1.Open strSQL, Connstring1, 1,1
IF rsserver1.eof and rsserver1.bof THEN
Msgbox "No Rows to Compare in Table 1"
rsserver1.close
SET rsserver1 = NOTHING
wscript.quit
END IF

SET rsserver2 = CreateObject("adodb.recordset")
rsserver2.Open strSQL, Connstring2, 1,1
IF rsserver2.eof and rsserver2.bof THEN
Msgbox "No Rows to Compare in Table 2"
rsserver1.close
SET rsserver1 = NOTHING
rsserver2.close
SET rsserver2 = NOTHING
wscript.quit
END IF

IF rsserver1.GetString() = rsserver2.GetString() THEN
Result = TRUE
ELSE
Result = FALSE
END IF

rsserver1.close
SET rsserver1 = NOTHING
rsserver2.close
SET rsserver2 = NOTHING

MsgBox Result

END SUB

Although VBScript is a favorite of mine, I’m a bit surprised that it’s still a requirement on new project. Anyway, thanks for the distraction, friend. It was nice to get away from VM troubleshooting for a bit.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SQL Server, VBScript

Leave a Reply

Your email address will not be published. Required fields are marked *

*