What are the challenges you face when working across database platforms? Take the survey

Compare the whole instance

nichwangnichwang Posts: 5
edited May 11, 2006 2:43AM in SQL Compare Previous Versions
Hi, one of our system installed 10 copies, each copy use seperated DB instance, and each instance has about 20 database.
We need to periodly compare schema of all these instances.
SQL compare allows choose instance then database, which means we have to setup 200 projects... which is too tedious..

Would you provide any solutions on this issue?


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1

    You could use the command-line version and a batch file. The information about the instances of SQL Server is in the server's registry and the list of databases is in each server's sysdatabases table (I believe sys.databases in SQL 2005). So for instance, you could write a VBScript that could do all of this:

    1. RegRead HKLM\Software\Microsoft\Microsoft SQL Server\InstalledInstances. This returns an array of instance names
    2. Use an ADO connection to run a query against the master database of each server for the list of databases:

    SELECT Name from sysdatabases where name NOT IN ('master','model','msdb')

    3. Your own logic to decide what gets compared against what.
    4. Use SQLCompare.exe with the proper arguments.
  • Options
    It seems work, thanks :)
This discussion has been closed.