Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

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 SqlA as (
    select
         o1.name
        ,o1.type
        ,o1.type_desc
        ,s1.definition
    from
        AAA_Dev.sys.objects o1
        inner join AAA_Dev.sys.sql_modules s1
            on o1.object_id = s1.object_id
    )
,SqlB as (
    select
         o1.name
        ,o1.type
        ,o1.type_desc
        ,s1.definition
    from
        AAA.sys.objects o1
        inner join AAA.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
    SqlA a
    left join SqlB b
        on a.name = b.name
where 1=1
    and (b.name is null or b.definition <> a.definition)
order by 
    1