How to compare SQL databases ?

• stevenmillward

  SSC Veteran

  Points: 253

  More actions

  February 7, 2019 at 8:06 am

  #382700

  Hi
  Got an issue with a client where an import works for us using our DB created by our SQL files and fix pack SQL files. However the clients does not.
  I am thinking that there is a mismatch in the database somehow, but my problem is how can I get a copy of how they have their database set up?
  I've tried using the generate scripts option on our DB but when I re-create the database using this method mine always hangs in a certain position.
  Is there another way I can get their DB structure etc (not the data)
  ?

  thanks

  Steve

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 7, 2019 at 8:32 am

  #2021401

  stevenmillward - Thursday, February 7, 2019 8:06 AM

  Hi
  Got an issue with a client where an import works for us using our DB created by our SQL files and fix pack SQL files. However the clients does not.
  I am thinking that there is a mismatch in the database somehow, but my problem is how can I get a copy of how they have their database set up?
  I've tried using the generate scripts option on our DB but when I re-create the database using this method mine always hangs in a certain position.
  Is there another way I can get their DB structure etc (not the data)
  ?

  thanks

  Steve

  Easy way, get a third party tool like Redgate's SQL Compare (it's the one I've used).
  Hard way, compare the system views to look for differences in the schema. You could export the values into another database for ease of comparison.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• jekoch

  SSCertifiable

  Points: 5034

  More actions

  February 7, 2019 at 8:35 am

  #2021404

  You could query the metadata between their systems and yours to look for differences. To compare data types, run something like this on each system and compare results:

  SELECT OBJECT_NAME(sc.[id]) as TableName

  ,

  sc.[name] as ColName

  , sc.xtype as DatatYpe

  , sc.[length]

  , sc.[xprec]

  , sc.[xscale]

  , sc.[colid]

  , sc.[colorder]

  FROM sys.syscolumns sc

  JOIN sys.sysobjects so on so.id = sc.id and so.type = 'U';

  This will  indicate if there is a difference between your two systems that is causing the import to fail. You can use this approach against index tables as well to see if it is a constraint issue.