use master
go

IF OBJECT_ID('sp_ABCompareDb') IS NOT NULL
        DROP PROCEDURE dbo.sp_ABCompareDb
GO

CREATE PROCEDURE sp_ABCompareDb

/* Name: sp_ABCompareDb
 *
 * Filename: Personal/Source/sp_ABCompareDb.sql
 *
 * Purpose:  Compare a source and target database.  List all procedures, tables, views, and triggers
 *           on the source that either don't exist on the target, or are later on the source than the 
 *           target machine.  This is useful when a series of changed objects need to be installed from 
 *           a development server to a production server.
 *
 * Called by: N/A
 *
 * Written by:  Vince Iacoboni
 *
 * Modification history:
 *  08/17/1998  VRI  Created.
 *
 */

@SourceServer   varchar(30),
@TargetServer   varchar(30),
@SourceDatabase varchar(30),
@TargetDatabase varchar(30) = null,
@SourceSAPassword varchar(30) = null,
@TargetSAPassword varchar(30) = null

AS

    DECLARE @Cmd1 varchar(255), @Cmd2 varchar(255), @Pw varchar(35), @IsqlPath varchar(100)

    CREATE TABLE #Target (ObjName varchar(30), ObjType char(2), CrDate datetime)
    CREATE TABLE #Source (ObjName varchar(30), ObjType char(2), CrDate datetime)
    CREATE TABLE #TargetText (LineNum int IDENTITY, LineText varchar(255) NULL)
    CREATE TABLE #SourceText (LineNum int IDENTITY, LineText varchar(255) NULL)

    IF @SourceSAPassword IS NOT NULL
        SELECT @SourceSAPassword = ' -P' + @SourceSAPassword
    ELSE
        SELECT @SourceSAPassword = ' -E'  -- assume trusted connection if no password given

    IF @TargetSAPassword IS NOT NULL
        SELECT @TargetSAPassword = ' -P' + @TargetSAPassword
    ELSE
        SELECT @TargetSAPassword = ' -E'  -- assume trusted connection if no password given

    IF ISNULL(@TargetDatabase, '') = '' 
            SELECT @TargetDatabase = @SourceDatabase


    /* get the first part of the ISQL.EXE path from the registry Setup key */
    EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\Setup', 
                               'SQLPath', @param=@IsqlPath OUTPUT
           
    SELECT @IsqlPath = @IsqlPath + '\BINN\ISQL.EXE' 

 
    /* Set up the command line to run ISQL with a command to list most objects */
    SELECT  @Cmd1 = @IsqlPath + ' -Usa' + @TargetSAPassword + ' -d' + @TargetDatabase + ' -S' + @TargetServer + ' -h-1 -Q',
            @Cmd2 = '""SET NOCOUNT ON ' + 
                    'SELECT name, type, crdate = convert(char(11), crdate, 101) + convert(char(12), crdate, 14) ' +
                    '  FROM sysobjects' + 
                    '  WHERE type in (''U'', ''V'', ''TR'', ''P'')' +
                    '  ORDER BY type, name""'

    /* run the command for the target machine and save the output */
    INSERT #TargetText (LineText)
      EXEC ('EXEC master..xp_cmdshell "' + @Cmd1 + @Cmd2 + '"')

    /* Modify for the source machine and save its output */
    SELECT  @Cmd1 = @IsqlPath + ' -Usa' + @SourceSAPassword + ' -d' + @SourceDatabase + ' -S' + @SourceServer + ' -h-1 -Q'
    INSERT #SourceText (LineText)
      EXEC ('EXEC master..xp_cmdshell "' + @Cmd1 + @Cmd2 + '"')


    /* convert the output into columns */
    INSERT #Target
      SELECT  substring(LineText, 2,30),
              substring(LineText,33,2),
              substring(LineText,38,30)
        FROM  #TargetText
        WHERE LineNum >= 3  -- skip the heading and dash line
          AND LineText IS NOT NULL

    INSERT #Source
      SELECT  substring(LineText, 2,30),
              substring(LineText,33,2),
              substring(LineText,38,30)
        FROM  #SourceText
        WHERE LineNum >= 3  -- skip the heading and dash line
          AND LineText IS NOT NULL


    /* look for objects in source but not target, or later on source than target */
    SELECT #Source.ObjName,
           #Source.ObjType,
           #Source.CrDate SourceDate,
           #Target.CrDate TargetDate
      FROM #Source LEFT OUTER JOIN 
           #Target ON  #Source.ObjName = #Target.ObjName 
                   AND #Source.ObjType = #Target.ObjType
      WHERE (#Target.ObjName IS NULL
        OR #Target.CrDate < #Source.CrDate)
      ORDER BY #Source.ObjType, 
               #Source.ObjName

    DROP TABLE #Source, 
               #Target,
               #SourceText,
               #TargetText
GO

GRANT EXECUTE ON dbo.sp_ABCompareDb TO public
GO