Compare two databases for objects differences

This SQL script will compare table columns, Index and foreign keys in two databases. This will show a result with missing objects and the objects with definition mismatch

SP_CompareDB.sql
 
 
 
 
 
4.2 Star
(12)
12,098 times
Add to favorites
Databases
5/8/2017
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Hit an error
    2 Posts | Last post September 15, 2017
    • Will do some research on it, but here is the message:
      
      Msg 515, Level 16, State 2, Procedure sp_CompareDb, Line 275 [Batch Start Line 567]
      Cannot insert the value NULL into column 'PK_TABLE', table 'tempdb.dbo.#FKLIST_SOURCE_
    • Found the bug.
      When populate "#FKLIST_SOURCE" it looks for "OBJECT_NAME" in @TargetDB
      
      Works fine after replacing by @SourceDB:
      -----------------------------------------------------------------
      With CTE
      				AS
      			(select OBJECT_NAME(FK.parent_object_id,db_id('''+@SourceDB+''')) PK_TABLE,	 
      						C1.name PK_COLUMN,
      			object_name(FK.referenced_object_id,db_id('''+@SourceDB+'''))FK_TABLE,
      			C2.name FK_COLUMN,
      			FK.name	 FK_NAME
      INSERT INTO #FKLIST_SOURCE(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS) 
          EXEC ('With CTE
      				AS
      			(select OBJECT_NAME(FK.parent_object_id,db_id('''+@SourceDB+''')) PK_TABLE,	 
      						C1.name PK_COLUMN,
      			object_name(FK.referenced_object_id,db_id('''+@SourceDB+'''))FK_TABLE,
      			C2.name FK_COLUMN,
      			FK.name	 FK_NAME
      ........
  • Compare database on different servers
    6 Posts | Last post January 25, 2016
    • I was wondering if you could enhance this script to compare databases deployed to different servers. For instance in our environment we have a Dev, QA, UAT, PreProd & Prod boxes and this script would come in handy.
      
      Thanks.................
    • Thanks, That's a good suggestion. I will work on this.
    • With 3 changes and a linked server that can be done:
      
      1) Edit the first part of the INSERT INTO #FKLIST_SOURCE to not use object_name:
      	EXEC ('With CTE
      				AS
      (select pkt.name PK_TABLE,
      	C1.name PK_COLUMN,
      	fkt.name FK_TABLE,
      	C2.name FK_COLUMN,
      	FK.name	 FK_NAME
      from ['+@SourceDB+'].sys.foreign_keys FK
      inner join ['+@SourceDB+'].sys.foreign_key_columns FKC
      	on FK.object_id=FKC.constraint_object_id
      inner join ['+@SourceDB+'].sys.columns C1 
      	on FKC.parent_column_id=C1.column_id
      	and FKC.parent_object_id=C1.object_id
      inner join ['+@SourceDB+'].sys.columns C2
      	on FKC.referenced_column_id=C2.column_id
      	and FKC.referenced_object_id=C2.object_id
      inner join ['+@SourceDB+'].sys.tables pkt 
      	on FK.parent_object_id = pkt.object_id 
      inner join ['+@SourceDB+'].sys.tables fkt 
      	on FK.referenced_object_id = fkt.object_id 
      	)
      ...
      
      2) Edit INSERT INTO #FKLIST_TARGET( in the same way
      
      3) Fix (left as an exercise for the reader...) or comment out the DB checks at the top:
      --IF DB_ID(@SourceDB) IS NULL 
      --BEGIN
      --PRINT 'Error: Unable to find the database '+ @SourceDB +'!!!'
      --RETURN
      --END
      
      4) Add the other server as a linked server.
      See: http://msdn.microsoft.com/en-us/library/ff772782(v=sql.105).aspx
      
      Usage might be like:
      
      -- link ONCE ONLY!
      exec sp_addlinkedserver @server=N'PreProd\instance'
      
      exec SP_Comparedb database, '[PreProd\instance].database'
    • Nice sample, thanks for sharing
    • Very useful tool.
    • Great update!
  • I doubt if it picks up for different schema objects
    2 Posts | Last post October 31, 2013
    • I have tested and it do not pick up the differences, if objects with different schema other than DBO.
      
    • Thanks Prakash. I would add this point as a limitation and at this moment I am not looking  at updating this script in very near future. I would consider this to be fixed though in future.
  • Some fixes
    1 Posts | Last post July 20, 2013
    • Very useful, thanks!
      
      Fixes:
      If a database name needs [quoting] (for example: DB_2017-07-18 has '-' characters and needs to be quoted with [] in SQL statements), the script fails, so:
      Lines 180:181:185:226:227:230:233:284:286:289:293: replace '+@SourceDB+' with ['+@SourceDB+']
      
      Lines 203:204:208:254:255:258:261:316:318:321:325: replace '+@TargetDb+' with ['+@TargetDb+']
      
      Lines 278 & 280 (#FKLIST_SOURCE), replace +@TargetDb+ with +@SourceDB+
  • Possible error?
    2 Posts | Last post July 11, 2013
    • On Line 25, i noticed it says:
      
      IF DB_ID(@SourceDB) IS NULL
      
      Is that not supposed to be @TargetDB instead?
    • Yup, that's an error. I have updated it. Thanks