--sp_addlinkedserver @server = '____________'
--sp_addlinkedserver @server = '____________'
--select * from sysservers


--sp_addlinkedserver 
--    '____________', 
--    'Oracle', 
--    'MSDAORA', 
--    'ORC1'

--Select * from ___________.ORC1.dbo.sysobjects

/* Objects in Company1 Missing in Company2 */
Select 'Table Objects in Company1 but are not in Company2'
select Left(a.name,30), a.refdate from sysobjects a 
Where a.xtype = 'U' 
and a.name like 'TBL%'
and Not Exists (Select 1 From ____________.dbname.dbo.sysobjects b where a.name = b.name)

/* Objects in Company2 Missing in Company1 */
Select 'Table Objects in Company2 but are not in Company1' 
select Left(a.name,30), a.refdate from ____________.dbname.dbo.sysobjects a 
Where a.xtype = 'U' 
and a.name like 'TBL%'
and Not Exists (Select 1 From sysobjects b where a.name = b.name)

/*  Column Differences */

Select 'Column Differences between like named tables'

select Left(x.TabName,30) as TableName, Left(x.ColName,30) as ColumnName
     , Left(x.DataType,15) as Company1DataType, x.length as Company1Length, x.refdate as Company1RefDate
     , Left(y.DataType,15) as Company2DataType, y.length As Company2Length,  y.refdate as Company2RefDate
from 
     ( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
         from sysobjects a, syscolumns b, systypes c
	where a.id = b.id 
          and b.xusertype = c.xusertype
          and a.xtype = 'U' and a.name like 'TBL%') As x
,     ( Select a.name as TabName, b.name as ColName, b.length,  c.name as DataType, a.refdate
          from ____________.dbname.dbo.sysobjects a, ____________.dbname.dbo.syscolumns b, ____________.dbname.dbo.systypes c
	where a.id = b.id and a.xtype = 'U'
          and b.xusertype = c.xusertype 
          and a.name like 'TBL%') As y
Where  x.TabName = y.TabName 
  and  x.ColName = y.ColName 
  and (x.length <> y.length or x.DataType <> y.DataType)

/*  Column Differences */
Select 'Column in Company1.com not in Company2'

Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
  from sysobjects a, syscolumns b, systypes c
 where a.id = b.id 
   and b.xusertype = c.xusertype
   and a.xtype = 'U' 
   and a.name like 'TBL%'
   and Not Exists (
        Select 1
          from ____________.dbname.dbo.sysobjects d, ____________.dbname.dbo.syscolumns e
	 where d.id = e.id 
	   and a.xtype = 'U'
           and a.name like 'TBL%'
           and a.name = d.name
  	   and b.name = e.name)
Order by 1, 2

/*  Column Differences */
Select 'Column in Company2 not in Company1.com'

Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
  from ____________.dbname.dbo.sysobjects a, ____________.dbname.dbo.syscolumns b, ____________.dbname.dbo.systypes c
 where a.id = b.id 
   and b.xusertype = c.xusertype
   and a.xtype = 'U' 
   and a.name like 'TBL%'
   and Not Exists (
        Select 1
          from sysobjects d, syscolumns e
	 where d.id = e.id 
	   and a.xtype = 'U'
           and a.name like 'TBL%'
           and a.name = d.name
  	   and b.name = e.name)
Order by 1, 2





--Select 'Table Objects that are still in use in both Company2 and Company1'
--select Left(a.name,30), a.refdate from sysobjects a, ____________.dbname.dbo.sysobjects b 
--where a.name = b.name and a.xtype = 'U'