Efficiently Compare Two SQL Server Tables Using Mostly VBScript

Jan 14, 2009 · 3 min read  ·
Share on:

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

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