Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

Page History: Code Object Differences Between Databases - SQL Server

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: Sun, Feb 01, 2009, 9:06 PM


The following SQL statement will list the differences in the code objects between two databases.

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)

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.