Efficiently Compare Two SQL Server Tables Using Mostly VBScript

calendar Jan 14, 2009  · Updated: November 2, 2025 · 4 min read  ·
Share on: linkedin bluesky copy

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 reusable 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.)
 1strServer1 = "server1"
 2strServer2 = "server2"
 3strDatabaseName = "mydatabase"
 4strTableName = "mytable"
 5
 6Call CompareTables(strServer1, strServer2, strDatabaseName, strTableName)
 7
 8Sub CompareTables(server1, server2, databaseName, tableName)
 9
10  ' This is a SQL Server 2005 Connection String with Windows Authentication. Change as necessary.
11  connString1 = "Provider=SQLNCLI;Server=" & server1 & ";Database=" & databaseName & ";Integrated Security=SSPI;"
12  connString2 = "Provider=SQLNCLI;Server=" & server2 & ";Database=" & databaseName & ";Integrated Security=SSPI;"
13
14  ' 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.
15  ' Not only will this be useful in the SELECT statement (SELECT col1, col2, col3) but also is necessary for the ORDER BY.
16  ' Because we'll be using a text comparison, columns and order by must be in the same exact order on both server1 and server2
17  strSQLColumns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & tableName & "' " & _
18                  "AND (COLUMNPROPERTY(OBJECT_ID('" & tableName & "'), column_name, 'IsIdentity')) != 1 " & _
19                  "ORDER BY ORDINAL_POSITION ASC"
20
21  Set rsColumns = CreateObject("adodb.recordset")
22  rsColumns.Open strSQLColumns, connString1, 1, 1
23
24  If rsColumns.EOF And rsColumns.BOF Then
25    MsgBox "The table has no columns or does not exist."
26    rsColumns.Close
27    Set rsColumns = Nothing
28    WScript.Quit
29  Else
30    Do Until rsColumns.EOF
31      strColumns = strColumns & rsColumns("COLUMN_NAME") & ","
32      rsColumns.MoveNext
33    Loop
34    ' Take off last comma
35    strColumns = Left(strColumns, Len(strColumns) - 1)
36    rsColumns.Close
37    Set rsColumns = Nothing
38  End If
39
40  ' Ninja
41  strSQL = "SELECT " & strColumns & " FROM " & tableName & " ORDER BY " & strColumns & " ASC"
42
43  Set rsServer1 = CreateObject("adodb.recordset")
44  rsServer1.Open strSQL, connString1, 1, 1
45  If rsServer1.EOF And rsServer1.BOF Then
46    MsgBox "No Rows to Compare in Table 1"
47    rsServer1.Close
48    Set rsServer1 = Nothing
49    WScript.Quit
50  End If
51
52  Set rsServer2 = CreateObject("adodb.recordset")
53  rsServer2.Open strSQL, connString2, 1, 1
54  If rsServer2.EOF And rsServer2.BOF Then
55    MsgBox "No Rows to Compare in Table 2"
56    rsServer1.Close
57    Set rsServer1 = Nothing
58    rsServer2.Close
59    Set rsServer2 = Nothing
60    WScript.Quit
61  End If
62
63  If rsServer1.GetString() = rsServer2.GetString() Then
64    Result = True
65  Else
66    Result = False
67  End If
68
69  rsServer1.Close
70  Set rsServer1 = Nothing
71  rsServer2.Close
72  Set rsServer2 = Nothing
73
74  MsgBox Result
75
76End Sub

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