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?
Thanks!
· Share on Twitter

Comments

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

    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.
    · Share on Twitter
  • nichwangnichwang Posts: 5
    It seems work, thanks :)
    · Share on Twitter
This discussion has been closed.