Compare databases between 2 SQL servers

[rcamarda]

Hello,
I wish to see if the tables from database A server A1 exist in database
A on server B1.
I setup a linked server from my first server (robertcamarda) to a
target (cognos-dev)
This works from robertcamarda:
select count(*) from [cognos-dev].ds_v6_source.dbo.stdmas

and this works:
select * from ds_v6_source.information_schema.tables order by
table_name

but this wont work:
select * from [cognos-dev].ds_v6_source.information_schema.tables order
by table_name

Error:
Msg 117, Level 15, State 1, Line 1
The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'
contains more than the maximum number of prefixes. The maximum is 3.

I want to do something like:
select * from ds_v6_source.information_schema.tables
where table_name not in (select table_name from
[cognos-dev].ds_v6_source.information_schema.tables order by
table_name)

so I can see of the sql server (robertcamarda) has any missing tables
that exist on the server (cognos-dev)
TIA
Rob
SQL Server 2005 Enterprise

[raj.raghavan]

use sqlcompare tool to compare anything and everything between two
databases

www.red-gate.com

download the trial version and see if it works for you.

[Jeff Kish]

On 17 May 2006 05:30:36 -0700, "rcamarda" <ro*****@hotmail.com> wrote:

Hello,
I wish to see if the tables from database A server A1 exist in database
A on server B1.

winsql has a trial that is pretty useful
hth
Jeff Kish

[Erland Sommarskog]

rcamarda (ro*****@hotmail.com) writes:

but this wont work:
select * from [cognos-dev].ds_v6_source.information_schema.tables order
by table_name

Error:
Msg 117, Level 15, State 1, Line 1
The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'
contains more than the maximum number of prefixes. The maximum is 3.

There is an apparent inconsistency between the error message and the
command you presented.

Then again, I was not able to get it to work with the proper command
either. This is probably because the INFORMATION_SCHEMA views are
not present in the databases, only in master. (Hm, I believe they
were in SQL 7, so with if you connect to SQL 7 it might work.)

Rather than using INFORMATION_SCHEMA, use the system tables or
the catalog views depending on which version of SQL Server you
are on.

SELECT name FROM [cognos_dev].ds_v6_source.dbo.sysobjects
WHERE type = 'U'
ORDER BY name

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx