Microsoft Business Intelligence: Compare DB objects between two Databases in sql server

Friday 28 May 2021

Compare DB objects between two Databases in sql server

We have two databases that point to the two different environments. Sometimes we are doing the change in one DB due to that we both DB is not in Sync. We need to identify the changed objects.

There are below ways to identify the missing objects.

· Either we can check manually and identify the missing object. This is a very difficult task and we will not get the correct result.

· Using the sql script we can get the missing objects. It is very easy and we will get the correct data.

Below is the script to find the missing data.

DECLARE @SourceDatabase VARCHAR(50),
        @TargetDatabase VARCHAR(50),
        @SqlScript      NVARCHAR(max),
        @return_val     BIGINT;

SET @return_val=0;
SET @SourceDatabase = 'AdventureWorks2012';
SET @TargetDatabase = 'AdventureWorks2012_UAT';

SELECT @SqlScript =
       'Select ObjectName,Status from ( SELECT TOP 100 PERCENT ISNULL(S.name,D.name) ObjectName ,CASE   WHEN S.object_id IS NULL THEN D.type_desc + '' is missing in the Source Database: '
       + @SourceDatabase
       +
       ''' WHEN D.object_id IS NULL THEN S.type_desc +  '' is missing in the Destination Database: '
                    + @TargetDatabase
                    + ''' END ''Status'' FROM ( SELECT object_id,name,type,type_desc FROM '
                    + @SourceDatabase
                    + '.SYS.objects   ) AS S   FULL OUTER JOIN ( SELECT object_id,name,type,type_desc FROM '
                    + @TargetDatabase + '.SYS.objects   ) AS D ON S.name = D.name AND S.type = D.type ORDER BY isnull(S.type,D.type) ) x where status is not null '

EXEC @return_val=sys.Sp_executesql
  @stmt=@SqlScript

Running this script.

As of now we both db are in sync.

Now we are creating one object in the AdventureWorks2012 DB.

USE [AdventureWorks2012]
go
CREATE PROCEDURE [dbo].[P_get_emp_detail] @year INT
AS
  BEGIN
      SELECT P.firstname,
             P.lastname,
             E.jobtitle,
             E.gender
      FROM   humanresources.employee E
             INNER JOIN person.person P
                     ON E.businessentityid = P.businessentityid
      WHERE  Datediff(year, hiredate, Getdate()) >= @year
  END;

go

Now we are running the above script.

See we get the missing object details.

Let’s see one more example now we are creating the new object in the AdventureWorks2012_UAT DB.

USE [AdventureWorks2012_UAT]
go
CREATE PROCEDURE [dbo].[P_get_emp_detail_uat] @year INT
AS
  BEGIN
      SELECT P.firstname,
             P.lastname,
             E.jobtitle,
             E.gender
      FROM   humanresources.employee E
             INNER JOIN person.person P
                     ON E.businessentityid = P.businessentityid
      WHERE  Datediff(year, hiredate, Getdate()) >= @year
  END;
go