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

Now we are running the above script.  

See we get the missing object details.

Now doing the sync in both DBs.

                                        
After Sync we will see get the above result.

Script to find the number of Columns in the table in Sql Server

 Using the sql script we will get the number of columns in the database.

;WITH cte(object_id, number_of_columns)      AS (SELECT object_id,                 Count(*)          FROM   sys.columns          GROUP  BY object_id) SELECT s.NAME + '.' + t.NAME AS TableName,        cte.number_of_columns FROM   cte        INNER JOIN sys.tables AS t                ON cte.object_id = t.object_id        INNER JOIN sys.schemas AS s                ON t.schema_id = s.schema_id ORDER  BY cte.number_of_columns DESC

See the result.

 

Temp table mystery: Getting SQL server Error# Msg 2714 & 1750 there is already an object named #temp table ('Pk_id') in the database & could not create constraint. See previous errors.

 We have a sp in which we have created local temp table and this SP is used in the worker process (micro service developed in the c#). This process call this SP. In a millisecond it makes multiple hit.

 We have created the primary key constraint in the temp table. Some time we are getting the below error.  

Let’s replicating this issue

We are creating same temp table in two query windows.

IF Object_id('tempdb..#t', 'U') IS NOT NULL   DROP TABLE #t go CREATE TABLE #t   (      id   INT IDENTITY(1, 1) NOT NULL,      NAME VARCHAR(100),      CONSTRAINT pk_id PRIMARY KEY CLUSTERED (id)   )

When we run this script in first window the temp table is created successfully. 

When we are running this same code in other window we will get the below error.    

The reason behind it: we can’t create two constraints with the same name.  In the above scenario we are creating two temp table but we are trying to create the Primary key constrain with the same name (Pk_id). Due to this we are getting this error.

 To overcoming this issue below are the approaches

·         Create temp table without named constraint

·         Use table variable if we are getting less than 1000 records.

See here we are creating the temp table without named constrain.

IF Object_id('tempdb..#t', 'U') IS NOT NULL   DROP TABLE #t go CREATE TABLE #t   (      id   INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,      NAME VARCHAR(100)   )

Running this script in the first window.  

Running the same script into the other window  

Now we are not getting the error.

 If we use the variable table in this case we will not get such type of error.

See the example of the variable table.

DECLARE @t TABLE   (      id   INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,      NAME VARCHAR(100)   )

Running the code in the first window  

Running this code in the second window 

Now we are not getting the error.

Temp table mystery: Shall we call the Parent Temp table into the Child SP

Yes, we can call the Parent temp table in the child SP. We can’t call the Child SP Temp table in the parent SP.

Let’s see this demo.

Here we have created two 2SP

Child SP

CREATE PROCEDURE [dbo].[P_child_sp_demo] AS   BEGIN       CREATE TABLE #child_temp         (            id   INT IDENTITY(1, 1) PRIMARY KEY,            NAME VARCHAR(50),            eadd VARCHAR(100)         )       INSERT INTO #child_temp                   (NAME,                    eadd)       SELECT 'Child SP Bagesh',              'Noida'       UNION ALL       SELECT 'Child SP Rajesh',              'Pune'       SELECT *       FROM   #child_temp   END;

Running this SP.

 

 Now creating the Parent SP

CREATE PROCEDURE [dbo].[P_parent_sp_demo] AS   BEGIN       DECLARE @return_val INT =0;       CREATE TABLE #parent_temp         (            id   INT IDENTITY(1, 1) PRIMARY KEY,            NAME VARCHAR(50),            eadd VARCHAR(100)         )       INSERT INTO #parent_temp                   (NAME,                    eadd)       SELECT 'Parent SP Bagesh',              'Noida'       UNION ALL       SELECT 'Parent SP Rajesh',              'Pune'       SELECT *       FROM   #parent_temp       ------calling the another SP P_Child_SP_Demo       EXEC @return_val=P_child_sp_demo       IF @return_val <> 0         BEGIN             RAISERROR(        'there is some error while calling the SP P_child_sp_demo',16,1             );         END   END;

   

Now we are calling the Temp table in the Child SP which we have created in the Parent table.

Altering the child SP

ALTER PROCEDURE [dbo].[P_child_sp_demo] AS   BEGIN       CREATE TABLE #child_temp         (            id   INT IDENTITY(1, 1) PRIMARY KEY,            NAME VARCHAR(50),            eadd VARCHAR(100)         )       INSERT INTO #child_temp                   (NAME,                    eadd)       SELECT 'Child SP Bagesh',             'Noida'       UNION ALL       SELECT 'Child SP Rajesh',             'Pune'       SELECT *      FROM   #child_temp       SELECT *      FROM   #parent_temp   END;

 Now we are calling the Parent SP.

SP executed successfully.

  

If we execute only child P alone we will get the error.  

When we call the child SP temp table we are getting the error. Altering the Parent SP.

ALTER PROCEDURE [dbo].[P_parent_sp_demo] AS   BEGIN       DECLARE @return_val INT =0;       CREATE TABLE #parent_temp         (            id   INT IDENTITY(1, 1) PRIMARY KEY,            NAME VARCHAR(50),            eadd VARCHAR(100)         )       INSERT INTO #parent_temp                   (NAME,                    eadd)       SELECT 'Parent SP Bagesh',              'Noida'       UNION ALL       SELECT 'Parent SP Rajesh',              'Pune'       SELECT *       FROM   #parent_temp       ------calling the another SP P_Child_SP_Demo       EXEC @return_val=P_child_sp_demo       SELECT *      FROM   #child_temp       IF @return_val <> 0         BEGIN             RAISERROR(             'there is some error while calling the SP P_Insert_App_Audit',16             ,1             );         END   END;

Running this SP.