Is there an easy way to compare the contents of objects between 2 different databases?

• sqlguy-736318

  Hall of Fame

  Points: 3859

  More actions

  July 20, 2015 at 5:10 pm

  #298900

  Hi, Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:

  use My_DB_1

  select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

  use My_DB_2

  select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

  Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 20, 2015 at 5:45 pm

  #1813762

  theres some really good db comparison tools out there, like redgatecompare, or dbdiff , or a database schema comparison if you have the ultimate version of visual studio. they do a fantastic job.

  for something quick and dirty, you could use the EXCEPT operator, but you want to compare in both directions, i think: since object_id's are going to be different regardless, you want to ge tthe list of object_names and columns, i think.

  select ColumnListWithoutObjectID from DB1.sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

  EXCEPT

  select ColumnListWithoutObjectID from DB2.sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

  select ColumnListWithoutObjectID from DB2.sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

  EXCEPT

  select ColumnListWithoutObjectID from DB1.sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!