From: obiron via on
Hi guys.

I am trying to compare the db objects on our new 2008 server with those on
the 2005 server. we have the 2005 server set up as a linked server using NT

here is my query to get all of the objects which don't match
with bfld2008 as
select o.object_ID,coalesce(OBJECT_DEFINITION(o.[object_id]), as
'description',, o.type,o.type_desc from [sys].objects o left join sys.
tables t on o.object_id = t.object_id where o.type in ('P','V','IF','FN',
, bfld2005 as
select o.object_ID,coalesce(OBJECT_DEFINITION(o.[object_id]), as
'description',, o.type,o.type_desc from [lic-py-bf-01].bluefish.[sys].
objects o left join [lic-py-bf-01].bluefish.sys.tables t on o.object_id = t.
object_id where o.type in ('P','V','IF','FN','TR','U')

bfld2008.object_id as '08_obj',
bfld2008.description as '08_desc', as '08_name',
bfld2008.type as '08_type',
bfld2008.type_desc as '08_td',
bfld2005.object_id as '05_obj',
bfld2005.description as '05_desc', as '05_name',
bfld2005.type as '05_type',
bfld2005.type_desc as '05_td'
full outer join
bfld2005 on bfld2008.description collate
SQL_Latin1_General_CP1_CI_AS = bfld2005.description collate
SQL_Latin1_General_CP1_CI_AS-- where bfld2005.description <> bfld2008.
bfld2005.object_id is null or bfld2008.object_id is null
order by
coalesce( collate SQL_Latin1_General_CP1_CI_AS , bfld2005.
name collate SQL_Latin1_General_CP1_CI_AS )

I am getting some mismatched data because the object id in 2005 is not the
same as the object id in 2008, but i am always trying to get the
OBJECT_DEFINITION from the 2008 database.

How can I access the remote OBJECT_DEFINITION() of the linked server database.

If I try to 4 part name it (i.e. [LIC-PY-BF-01].bluefish.sys.
object_definition(objectID) then I get the error message
Remote function reference 'LIC-PY-BF-01.bluefish.sys.object_definition' is
not allowed, and the column name 'LIC-PY-BF-01' could not be found or is

Message posted via

From: obiron via on
Thanks for that ML, That solution occurred to me on the way home.

Although it is possible, it will result in a record being reported because
the view will exist in one database and not the other.


Message posted via