SQL Compare Databases

[Marcster]

Does anyone know a SQL Server script to compare one database with another?.

So, compare DB1 against DB2 and list what table exists in DB1 but not DB2?

Thanks,

[CreganTur]

The only way I can think to do this is via VBA- using connections to pull out table lists into recordsets and then comparing them. If you'd like some example code for this, let me know- I'll have to dig it up.

[Marcster]

Hi Randy,

If you could find the code, great :-)
I've been looking at Microsoft Stored Procedures sp_helpdb etc...

[CreganTur]

This code will allow you to create a list of tables in a database. You'll need to set a reference to Microsoft ADO ext 2.8 for DDL and Security (your version number may be different). You'll also need to change the code so it puts the values into a recordset, instead of displaying them in the Immediate window:
[VBA]Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
cat.ActiveConnection = 'enter the connection string for your database here

'loop through all tables and display info
For Each tbl In cat.Tables
If tbl.Type <> "VIEW" AND tbl.Type <> "SYSTEM TABLE" AND _
tbl.Type <> "ACCESS TABLE" Then Debug.Print tbl.Name
Next tbl

'cleanup
set cat = Nothing
MsgBox "Data Gathered: View Immediate Window for tables list."[/VBA]

In Theory, you would need to populate 2 different recordsets, one for each database, then you just need to compare the recordsets.

HTH