select distinct 
    case odev.type
        when 'C'  then 'check constraint'
        when 'D'  then 'default'
        when 'F'  then 'foreign key'
        when 'FN' then 'function'
        when 'IF' then 'function'
        when 'K'  then 'primary key'
        when 'P'  then 'stored procedure'
        when 'S'  then 'system table'
        when 'TF' then 'function'
        when 'TR' then 'trigger'
        when 'U'  then 'user table'
        when 'V'  then 'view'
        else odev.type
        end as type_ext, 
    odev.name,
    'Created' as State

from 
    DevDb.dbo.sysobjects odev
    left join ProdDb.dbo.sysobjects oprod
        on odev.name = oprod.name
        and odev.type = oprod.type
where 1=1
    and oprod.name is null
    and not (odev.name like 'dt[_]%')

union select distinct 
    case odev.type
        when 'C'  then 'check constraint'
        when 'D'  then 'default'
        when 'F'  then 'foreign key'
        when 'FN' then 'function'
        when 'IF' then 'function'
        when 'K'  then 'primary key'
        when 'P'  then 'stored procedure'
        when 'S'  then 'system table'
        when 'TF' then 'function'
        when 'TR' then 'trigger'
        when 'U'  then 'user table'
        when 'V'  then 'view'
        else odev.type
    end as type_ext, 
    odev.name,
    'Changed' AS STATE

from 
    DevDb.dbo.sysobjects odev
    inner join DevDb.dbo.syscomments cdev
        on odev.id = cdev.id
    inner join ProdDb.dbo.sysobjects oprod
        on odev.name = oprod.name
    inner join ProdDb.dbo.syscomments cprod
        on oprod.id = cprod.id

where 1=1
    and cdev.text <> cprod.text 
    and replace(replace(replace(replace(replace( cdev.text,'  ',' '), char(10), ''), char(13), ''), char(9), ''), '  ', '') <> 
        replace(replace(replace(replace(replace(cprod.text,'  ',' '), char(10), ''), char(13), ''), char(9), ''), '  ', '')
    and odev.type not in ('S')
    and not (odev.name like 'dt[_]%')

order by 1, 2

option (robust plan)

;with SourceSqlCode as (
    select
         o1.name
        ,o1.type
        ,o1.type_desc
        ,s1.definition
    from
        SourceDatabase.sys.objects o1
        inner join SourceDatabase.sys.sql_modules s1
            on o1.object_id = s1.object_id
    )
,TargetSqlCode as (
    select
         o1.name
        ,o1.type
        ,o1.type_desc
        ,s1.definition
    from
        TargetDatabase.sys.objects o1
        inner join TargetDatabase.sys.sql_modules s1
            on o1.object_id = s1.object_id
    )  
select 
     a.name
    ,a.type
    ,a.type_desc
    ,Diff = case when b.name is null then 'Not deployed' 
                else 'Modified'
                end
from
    SourceSqlCode a
    left join TargetSqlCode b
        on a.name = b.name
where 1=1
    and (b.name is null or b.definition <> a.definition)
order by 
    1