if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_WhatsTheDiff]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_WhatsTheDiff]
GO

CREATE procedure usp_WhatsTheDiff
    @server1 varchar(255),
    @server2 varchar(255),
    @database varchar(255)
as

declare @sql nvarchar(4000)
declare @server1dotdb varchar(510)
declare @server2dotdb varchar(510)

set @server1dotdb = @server1 + '.' + @database
set @server2dotdb = @server2 + '.' + @database

create table #s1nots2
(  objname varchar(255), objtype varchar(255))
create table #s2nots1
(  objname varchar(255), objtype varchar(255))
create table #diff
(  objname varchar(255), objtype varchar(255))


SELECT @sql = 
N'INSERT INTO #s1nots2 (objname, objtype )
    select s1.name, s1.xtype
    from ' + @server1dotdb + '.dbo.sysobjects s1 left join ' + @server2dotdb + '.dbo.sysobjects s2
            on s1.name = s2.name
    where s1.xtype in (''P'',''TF'',''FN'',''TR'',''U'',''V'') and s2.id is null and s1.name not like ''dt_%'''
EXEC sp_executesql @sql

SELECT @sql = 
N'INSERT INTO #s2nots1 (objname, objtype )
    select s1.name, s1.xtype
    from ' + @server2dotdb + '.dbo.sysobjects s1 left join ' + @server1dotdb + '.dbo.sysobjects s2
            on s1.name = s2.name
    where s1.xtype in (''P'',''TF'',''FN'',''TR'',''U'',''V'') and s2.id is null and s1.name not like ''dt_%'''
EXEC sp_executesql @sql


SELECT @sql = 
N'INSERT INTO #diff (objname, objtype )
    select distinct so1.name, so1.xtype
    from ' + @server1dotdb + '.dbo.sysobjects so1 inner join ' + @server1dotdb + '.dbo.syscomments sc1 on so1.id = sc1.id
    where so1.xtype in (''P'',''TF'',''FN'',''TR'',''U'',''V'') and exists (
    select 1
    from ' + @server2dotdb + '.dbo.sysobjects so2 inner join ' + @server2dotdb + '.dbo.syscomments sc2 on so2.id = sc2.id
    where so1.name = so2.name and sc1.colid = sc2.colid and sc1.text <> sc2.text)
    and so1.name not like ''dt_%''
    option (ROBUST PLAN) '
EXEC sp_executesql @sql

SELECT objname 'Object Name',
       case objtype when 'P'  then 'Procedure'
                    when 'TF' then 'Function'
                    when 'FN' then 'Function'
                    when 'TR' then 'Trigger'
                    when 'U'  then 'Table'
                    when 'V'  then 'View'
                    else '?Error?'
        end as 'Object Type',
        'Missing in ' + @server2 as 'Status'
from #s1nots2
union
SELECT objname 'Object Name',
       case objtype when 'P'  then 'Procedure'
                    when 'TF' then 'Function'
                    when 'FN' then 'Function'
                    when 'TR' then 'Trigger'
                    when 'U'  then 'Table'
                    when 'V'  then 'View'
                    else '?Error?'
        end as 'Object Type',
        'Missing in ' + @server1 as 'Status'
from #s2nots1
union
SELECT objname 'Object Name',
       case objtype when 'P'  then 'Procedure'
                    when 'TF' then 'Function'
                    when 'FN' then 'Function'
                    when 'TR' then 'Trigger'
                    when 'U'  then 'Table'
                    when 'V'  then 'View'
                    else '?Error?'
        end as 'Object Type',
        'Dirty' as 'Status'
from #diff
order by 'Status', 'Object Type', 'Object Name'


drop table #s1nots2
drop table #s2nots1
drop table #diff

GO