Friday, December 2, 2011

Comparing 2 databases to find Objects existing in Database1 and not existing in Database2

Comparing 2 databases to find Objects existing in Database1 and not existing in Database2.


To Get UDD Differences :

-- NEW UDDs
select t.name,t.max_length,t.precision,t.scale,t.is_nullable,t1.name from Database1.sys.types t,Database1.sys.types t1 where t.is_user_defined=1 and t.name not in (select ti.name from Database2.sys.types ti where ti.is_user_defined=1)
AND t.system_type_id=t1.user_type_id

-- CHANGED UDDs
select t.name,tsys.name,t.max_length,t.precision,t.scale,t.is_nullable,t.default_object_id
,ttsys.name,tt.max_length,tt.precision,tt.scale,tt.is_nullable,tt.default_object_id
from Database1.sys.types t,Database2.sys.types tt,Database1.sys.types tsys,Database2.sys.types ttsys
where t.is_user_defined=1 and t.name=tt.name and t.system_type_id= tsys.user_type_id and tt.system_type_id=ttsys.user_type_id   
and t.name+'_'+CONVERT(NVARCHAR,t.system_type_id)+'_'+CONVERT(NVARCHAR,t.max_length)+'_'+CONVERT(NVARCHAR,t.precision)+'_'+CONVERT(NVARCHAR,t.scale)+'_'+CONVERT(NVARCHAR,t.is_nullable)+'_'+CONVERT(NVARCHAR,t.default_object_id)
not in (select ti.name+'_'+CONVERT(NVARCHAR,ti.system_type_id)+'_'+CONVERT(NVARCHAR,ti.max_length)+'_'+CONVERT(NVARCHAR,ti.precision)+'_'+CONVERT(NVARCHAR,ti.scale)+'_'+CONVERT(NVARCHAR,ti.is_nullable)+'_'+CONVERT(NVARCHAR,ti.default_object_id) from Database2.sys.types ti where ti.is_user_defined=1)
and t.name  not in (select n.name from Database1.sys.types n where n.is_user_defined=1 and n.name not in (select ni.name from Database2.sys.types ni where ni.is_user_defined=1) )


To Get Table Differences :

-- NEW TABLES
SELECT o.name FROM Database1.sys.objects o
WHERE o.type='U' and o.name not in (SELECT i.name FROM Database2.sys.objects i WHERE i.type='U')  order by o.name 

-- CHANGED COLUMNS
SELECT o.object_id,o.name,c.column_id,c.name,o.type,c.is_nullable,c.is_identity,t.name  
FROM Database1.sys.objects o,Database1.sys.columns c,Database1.sys.types t WHERE o.type ='U' and o.object_id=c.object_id and c.user_type_id=t.user_type_id 
AND o.name+'_'+  c.name NOT IN(SELECT uo.name+'_'+  uc.name FROM Database2.sys.objects uo,Database2.sys.columns uc WHERE uo.type ='U' and uo.object_id=uc.object_id) 
AND o.name not in (SELECT distinct n.name FROM Database1.sys.objects n
WHERE n.type='U' and n.name not in (SELECT ni.name FROM Database2.sys.objects ni WHERE ni.type='U'))
ORDER BY o.name,c.column_id  

To Get Constraint Differences :

-- DEFAULT CONSTRAINTS
select ob.name,o.name,c.name from Database1.sys.objects o,Database1.sys.columns c,Database1.sys.objects ob where o.type='D' and o.object_id=c.default_object_id and o.parent_object_id=ob.object_id  
and  ob.name+'_'+o.name+'_'+c.name not in (select obi.name+'_'+oi.name+'_'+ci.name from Database2.sys.objects oi,Database2.sys.columns ci,Database2.sys.objects obi where oi.type='D' and oi.object_id=ci.default_object_id and oi.parent_object_id=obi.object_id)  


-- PRIMARY KEY
select * from Database1.sys.objects where type='PK' and name not in(select name from Database2.sys.objects where type='PK')

1 comment:

  1. Hi,
    Nice article on comparing 2 databases to find the data existence .Thanks for sharing the knowledge with your users .
    Thank you.
    oracle R12 training

    ReplyDelete