Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member

Hi All,

I would like to Share this Comparison of Database Configuration.

How to Compare Two Database Configuration?

1.Compare - Formatted Searches

This query will compare 2 databases and display discrepancies if the formatted searches are configured differently.

--------------------------------------------------------------------------
-- Database Comparison - formatted Searches
-- Author - Bowya - SAP Business One Central
-- Version 1.0
--------------------------------------------------------------------------
SELECT CSHS.FormID, ItemId,ColID,QName,ActionT,Refresh,FieldID,ByField,FrceRfrsh
    FROM SBO_TEST.dbo.CSHS
        INNER JOIN OUQR ON CSHS.QueryId = OUQR.IntrnalKey
EXCEPT
    SELECT CSHS.FormID, ItemId,ColID,QName,ActionT,Refresh,FieldID,ByField,FrceRfrsh
        FROM vLIVE.dbo.CSHS
        INNER JOIN OUQR ON CSHS.QueryId = OUQR.IntrnalKey
        order by CSHS.formID
   
   
   
SELECT CSHS.FormID, ItemId,ColID,QName,ActionT,Refresh,FieldID,ByField,FrceRfrsh
    FROM SBO_LIVE.dbo.CSHS
        INNER JOIN OUQR ON CSHS.QueryId = OUQR.IntrnalKey
EXCEPT
    SELECT CSHS.FormID, ItemId,ColID,QName,ActionT,Refresh,FieldID,ByField,FrceRfrsh
        FROM SBO_TEST.dbo.CSHS
        INNER JOIN OUQR ON CSHS.QueryId = OUQR.IntrnalKey
        order by CSHS.formID  

2.Compare - Queries

This query will compare 2 databases and display discrepancies in the the queries name and SQL code.


--------------------------------------------------------------------------
-- Database Comparison - Queries
-- Author - Bowya - SAP Business One Central
-- Version 1.0
--------------------------------------------------------------------------
SELECT REPLACE(REPLACE(left(cast(Qstring as varchar(2000)),2000),' ',''),Char(13),'') 'QString', QName
    FROM SBO_TEST.dbo.OUQR
    WHERE QName NOT LIKE 'OPOR'
EXCEPT
    SELECT REPLACE(REPLACE(left(cast(Qstring as varchar(2000)),2000),' ',''),Char(13),'') 'QString', QName
    FROM SBO_LIVE.dbo.OUQR
Order By QName


SELECT REPLACE(REPLACE(left(cast(Qstring as varchar(2000)),2000),' ',''),Char(13),'') 'QString', QName
    FROM SBO_LIVE.dbo.OUQR
    WHERE QName NOT LIKE 'OPOR'
EXCEPT
    SELECT REPLACE(REPLACE(left(cast(Qstring as varchar(2000)),2000),' ',''),Char(13),'') 'QString', QName
    FROM SBO_TEST.dbo.OUQR
Order By QName

3.Compare - Tables

This query will compare 2 databases and warn when one particular Database has a table(s) more than the other one.

--------------------------------------------------------------------------
-- Database Comparison - Tables for DBs
-- Author - Bowya - SAP Business One Central
-- Version 1.0
--------------------------------------------------------------------------
DECLARE

    @DATABASE1 NVARCHAR(35),
    @DATABASE2 NVARCHAR(35)


SET @DATABASE1 = 'SBO_TEST'
SET @DATABASE2 = 'SBO_LIVE'




    DECLARE @COMMAND NVARCHAR(4000),
        @COLUMNS_TABLE1 NVARCHAR(200),
        @COLUMNS_TABLE2 NVARCHAR(200),
        @NAME NVARCHAR(50),
        @IS_NULLABLE VARCHAR(3),
        @DATA_TYPE NVARCHAR(128),
        @CHARACTER_MAXIMUM_LENGTH INT,
        @NUMERIC_PRECISION INT ,
        @NUMERIC_PRECISION_RADIX INT,
        @NUMERIC_SCALE TINYINT,
        @DATETIME_PRECISION SMALLINT,
        @TABLE NVARCHAR(50),
        @COUNT INT 

    SET @COUNT = 1
    SET @DATABASE1 =
        CASE
            WHEN  (@DATABASE1 IS NOT NULL) THEN '[' + RTRIM(@DATABASE1) + '].'
            WHEN (@DATABASE1 IS NULL) THEN ''
        END
    SET @DATABASE2 =
        CASE
            WHEN  (@DATABASE2 IS NOT NULL) THEN '[' + RTRIM(@DATABASE2) + '].'
            WHEN  (@DATABASE2 IS NULL) THEN ''
        END
   
    SET @COLUMNS_TABLE1 =  RTRIM(@DATABASE1) + '[INFORMATION_SCHEMA].[COLUMNS] '
    SET @COLUMNS_TABLE2 =  RTRIM(@DATABASE2) + '[INFORMATION_SCHEMA].[COLUMNS] '


    PRINT 'Exist in ' +@DATABASE1+ ' but not in ' +@DATABASE2
    PRINT ''

    SELECT @COMMAND =
        'DECLARE c CURSOR FOR
            SELECT
                Table_Name
            FROM '
                + @COLUMNS_TABLE1 + '
            WHERE Table_Name NOT LIKE ''ixv%''
                 AND table_Name NOT LIKE ''sysdiagrams''
            EXCEPT
            SELECT
                Table_Name
            FROM '
                + @COLUMNS_TABLE2

    BEGIN TRY    
        EXEC(@COMMAND);
    END TRY

    BEGIN CATCH
        PRINT 'ERROR_MESSAGE = ' + ERROR_MESSAGE() + ' ERROR LINE = ' + STR(ERROR_LINE())
        PRINT ''
    END CATCH

    OPEN c
    FETCH NEXT FROM
        c
    INTO
        @NAME

    WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                SET @COUNT = @COUNT + 1
                PRINT  'Table:  ' + @NAME
            END TRY

            BEGIN CATCH
                PRINT 'ERROR:  ' + ERROR_MESSAGE()
                PRINT ''
            END CATCH

            FETCH NEXT FROM
                c
            INTO
                @NAME
        END
    CLOSE c
    DEALLOCATE c

    PRINT 'Exist in ' +@DATABASE2+ ' but not in ' +@DATABASE1
    PRINT ''

    SELECT @COMMAND =
        'DECLARE c CURSOR FOR
            SELECT
                Table_Name
            FROM '
                + @COLUMNS_TABLE2 + '
            WHERE Table_Name NOT LIKE ''ixv%''
                AND table_Name NOT LIKE ''sysdiagrams''
           
            EXCEPT
            SELECT
                Table_Name
            FROM '
                + @COLUMNS_TABLE1
               
    BEGIN TRY    
        EXEC(@COMMAND);
    END TRY

    BEGIN CATCH
        PRINT 'ERROR_MESSAGE = ' + ERROR_MESSAGE() + ' ERROR LINE = ' + STR(ERROR_LINE())
        PRINT ''
    END CATCH

    OPEN c
    FETCH NEXT FROM
        c
    INTO
        @NAME
       
    SET @COUNT = 1

    WHILE @@FETCH_STATUS = 0

        BEGIN

            BEGIN TRY
                --PRINT 'Error ' + STR(@COUNT)
                PRINT 'Table:  ' + @NAME
                SET @COUNT = @COUNT + 1
            END TRY
           
            BEGIN CATCH
                PRINT 'ERROR:  ' + ERROR_MESSAGE()
            END CATCH

            FETCH NEXT FROM
                c
            INTO
                @NAME
        END

    CLOSE c
    DEALLOCATE c

4.Compare - UDFs Definitions for matching Tables

This query will compare 2 databases and provide warning when particular Table(s) has a UDF(s) created or configured differently in both the databases.


--------------------------------------------------------------------------
-- Database Comparison - UDFs Definitions from DBs on Existing Tables
-- Author - Bowya - SAP Business One Central
-- Version 1.0
--------------------------------------------------------------------------
DECLARE
    @DATABASE1 NVARCHAR(35),
    @DATABASE2 NVARCHAR(35)--,
    --@TABLE1 NVARCHAR(100),
    --@TABLE2 NVARCHAR(100)

SET @DATABASE1 = 'SBO_TEST'
SET @DATABASE2 = 'SBO_LIVE'


--SET @TABLE1 = 'OWHS'
--SET @TABLE2 = 'OWHS'

    DECLARE @COMMAND NVARCHAR(4000),
        @COLUMNS_TABLE1 NVARCHAR(200),
        @COLUMNS_TABLE2 NVARCHAR(200),
                @Table_TABLE1 NVARCHAR(200),
        @Table_TABLE2 NVARCHAR(200),
        @NAME NVARCHAR(50),
        @NAME2 NVARCHAR(50),
        @Type Nvarchar(3),
        @SizeID Nvarchar(3),
        @COUNT INT 

    SET @COUNT = 1
    SET @DATABASE1 =
        CASE
            WHEN  (@DATABASE1 IS NOT NULL) THEN '[' + RTRIM(@DATABASE1) + '].'
            WHEN (@DATABASE1 IS NULL) THEN ''
        END
    SET @DATABASE2 =
        CASE
            WHEN  (@DATABASE2 IS NOT NULL) THEN '[' + RTRIM(@DATABASE2) + '].'
            WHEN  (@DATABASE2 IS NULL) THEN ''
        END
   
    SET @COLUMNS_TABLE1 =  RTRIM(@DATABASE1) + 'dbo.CUFD'
    SET @COLUMNS_TABLE2 =  RTRIM(@DATABASE2) + 'dbo.CUFD'

    SET @Table_TABLE1 =  RTRIM(@DATABASE1) + '[INFORMATION_SCHEMA].[COLUMNS] '
    SET @Table_TABLE2 =  RTRIM(@DATABASE2) + '[INFORMATION_SCHEMA].[COLUMNS] '
--select @COLUMNS_TABLE1
--select Table_Name, * from [SBO_Test].[INFORMATION_SCHEMA].[COLUMNS]

    PRINT 'Exist in ' +@DATABASE1+ ' but not or different in ' +@DATABASE2
    PRINT ''

    SELECT @COMMAND =
        'DECLARE c CURSOR FOR
            SELECT
                TableID, AliasID, TypeID, SizeID
            FROM '
                + @COLUMNS_TABLE1 + '
            WHERE TableID NOT LIKE ''OPOR''
                AND TableID NOT LIKE ''OPDN''
                AND TableID NOT LIKE ''ORDN''
                AND TableID NOT LIKE ''OPCH''
                AND TableID NOT LIKE ''OQUT''
                AND TableID NOT LIKE ''ODLN''
                AND TableID NOT LIKE ''OWTR''
                AND TableID NOT LIKE ''OWOR''
                AND TableID NOT LIKE ''ODLN''
                AND TableID NOT LIKE ''ORIN''
                AND TableID NOT LIKE ''OINV''
                AND TableID NOT LIKE ''OIGN''
                AND TableID NOT LIKE ''OIGE''
                AND TableID NOT LIKE ''ODRF''
                AND TableID NOT LIKE ''ODPO''
                AND TableID NOT LIKE ''ORPC''
                AND TableID NOT LIKE ''ORPD''
                AND TableID NOT LIKE ''OCIN''
                AND TableID NOT LIKE ''OCPI''
                AND TableID NOT LIKE ''OCSI''
                AND TableID NOT LIKE ''OCSV''
                AND TableID NOT LIKE ''OCPV''
                AND TableID NOT LIKE ''ODPI''
                AND TableID NOT LIKE ''ADOC''
                AND TableID NOT LIKE ''AWOR''
                AND TableID NOT LIKE ''AACT''
            AND TableID NOT LIKE ''POR1''
                AND TableID NOT LIKE ''PDN1''
                AND TableID NOT LIKE ''RDN1''
                AND TableID NOT LIKE ''PCH1''
                AND TableID NOT LIKE ''QUT1''
                AND TableID NOT LIKE ''DLN1''
                AND TableID NOT LIKE ''WTR1''
                AND TableID NOT LIKE ''WOR1''
                AND TableID NOT LIKE ''DLN1''
                AND TableID NOT LIKE ''RIN1''
                AND TableID NOT LIKE ''ADO1''
                AND TableID NOT LIKE ''DPO1''
                AND TableID NOT LIKE ''DRF1''
                AND TableID NOT LIKE ''DPI1''
                AND TableID NOT LIKE ''RPD1''
                AND TableID NOT LIKE ''RPC1''
                AND TableID NOT LIKE ''CIN1''
                AND TableID NOT LIKE ''CPI1''
                AND TableID NOT LIKE ''CSI1''
                AND TableID NOT LIKE ''CSV1''
                AND TableID NOT LIKE ''CPV1''
                AND TableID NOT LIKE ''INV1''
                AND TableID NOT LIKE ''IGN1''
                AND TableID NOT LIKE ''IGE1''
            AND TableID NOT LIKE ''A%''
            AND TableID Not IN (
           
            SELECT
                Table_Name
            FROM '
                + @Table_TABLE1 + '
            WHERE Table_Name NOT LIKE ''ixv%''
                 AND table_Name NOT LIKE ''sysdiagrams''
            EXCEPT
            SELECT
                Table_Name
            FROM '
                + @Table_TABLE2 + ')


            EXCEPT
            SELECT
                TableID, AliasID,TypeID, SizeID
            FROM '
                + @COLUMNS_TABLE2

    BEGIN TRY    
        EXEC(@COMMAND);
    END TRY

    BEGIN CATCH
        PRINT 'ERROR_MESSAGE = ' + ERROR_MESSAGE() + ' ERROR LINE = ' + STR(ERROR_LINE())
        PRINT ''
    END CATCH

    OPEN c
    FETCH NEXT FROM
        c
    INTO
        @NAME,@NAME2,@Type,@SizeID

    WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                SET @COUNT = @COUNT + 1
                PRINT  'Table:  ' + @NAME + ' , ' +@NAME2 + ' , ' + @Type + ' , ' + @SizeID
            END TRY

            BEGIN CATCH
                PRINT 'ERROR:  ' + ERROR_MESSAGE()
                PRINT ''
            END CATCH

            FETCH NEXT FROM
                c
            INTO
                @NAME, @NAME2,@Type,@SizeID 
        END
    CLOSE c
    DEALLOCATE c

    PRINT 'Exist in ' +@DATABASE2+ ' but not or different in ' +@DATABASE1
    PRINT ''

    SELECT @COMMAND =
        'DECLARE c CURSOR FOR
            SELECT
                TableID, AliasID,TypeID, SizeID
            FROM '
                + @COLUMNS_TABLE2 + '
            WHERE TableID NOT LIKE ''OPOR''
                AND TableID NOT LIKE ''OPDN''
                AND TableID NOT LIKE ''ORDN''
                AND TableID NOT LIKE ''OPCH''
                AND TableID NOT LIKE ''OQUT''
                AND TableID NOT LIKE ''ODLN''
                AND TableID NOT LIKE ''OWTR''
                AND TableID NOT LIKE ''OWOR''
                AND TableID NOT LIKE ''ODLN''
                AND TableID NOT LIKE ''ORIN''
                AND TableID NOT LIKE ''OINV''
                AND TableID NOT LIKE ''OIGN''
                AND TableID NOT LIKE ''OIGE''
                AND TableID NOT LIKE ''ODRF''
                AND TableID NOT LIKE ''ODPO''
                AND TableID NOT LIKE ''ORPC''
                AND TableID NOT LIKE ''ORPD''
                AND TableID NOT LIKE ''OCIN''
                AND TableID NOT LIKE ''OCPI''
                AND TableID NOT LIKE ''OCSI''
                AND TableID NOT LIKE ''OCSV''
                AND TableID NOT LIKE ''OCPV''
                AND TableID NOT LIKE ''ODPI''
                AND TableID NOT LIKE ''ADOC''
                AND TableID NOT LIKE ''AWOR''
                AND TableID NOT LIKE ''AACT''
            AND TableID NOT LIKE ''POR1''
                AND TableID NOT LIKE ''PDN1''
                AND TableID NOT LIKE ''RDN1''
                AND TableID NOT LIKE ''PCH1''
                AND TableID NOT LIKE ''QUT1''
                AND TableID NOT LIKE ''DLN1''
                AND TableID NOT LIKE ''WTR1''
                AND TableID NOT LIKE ''WOR1''
                AND TableID NOT LIKE ''DLN1''
                AND TableID NOT LIKE ''RIN1''
                AND TableID NOT LIKE ''ADO1''
                AND TableID NOT LIKE ''DPO1''
                AND TableID NOT LIKE ''DRF1''
                AND TableID NOT LIKE ''DPI1''
                AND TableID NOT LIKE ''RPD1''
                AND TableID NOT LIKE ''RPC1''
                AND TableID NOT LIKE ''CIN1''
                AND TableID NOT LIKE ''CPI1''
                AND TableID NOT LIKE ''CSI1''
                AND TableID NOT LIKE ''CSV1''
                AND TableID NOT LIKE ''CPV1''
                AND TableID NOT LIKE ''INV1''
                AND TableID NOT LIKE ''IGN1''
                AND TableID NOT LIKE ''IGE1''
            AND TableID NOT LIKE ''A%''
            AND TableID NOT IN (
                SELECT
                Table_Name
            FROM '
                + @Table_TABLE2 + '
            WHERE Table_Name NOT LIKE ''ixv%''
                 AND table_Name NOT LIKE ''sysdiagrams''
            EXCEPT
            SELECT
                Table_Name
            FROM '
                + @Table_TABLE1 + ')

           
            EXCEPT
            SELECT
                TableID, AliasID,TypeID, SizeID
            FROM '
                + @COLUMNS_TABLE1
               
    BEGIN TRY    
        EXEC(@COMMAND);
    END TRY

    BEGIN CATCH
        PRINT 'ERROR_MESSAGE = ' + ERROR_MESSAGE() + ' ERROR LINE = ' + STR(ERROR_LINE())
        PRINT ''
    END CATCH

    OPEN c
    FETCH NEXT FROM
        c
    INTO
        @NAME,@NAME2,@Type,@SizeID
       
    SET @COUNT = 1

    WHILE @@FETCH_STATUS = 0

        BEGIN

            BEGIN TRY
                --PRINT 'Error ' + STR(@COUNT)
                PRINT  'Table:  ' + @NAME + ' , ' +@NAME2 + ' , ' + @Type + ' , ' + @SizeID
                SET @COUNT = @COUNT + 1
            END TRY
           
            BEGIN CATCH
                PRINT 'ERROR:  ' + ERROR_MESSAGE()
            END CATCH

            FETCH NEXT FROM
                c
            INTO
                @NAME, @NAME2,@Type,@SizeID
        END

    CLOSE c
    DEALLOCATE c


Thanks & Regards,

Bowya S S

1 Comment
Labels in this area