By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Compare databases between 2 SQL servers

P: n/a
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

May 17 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

May 17 '06 #2

P: n/a
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
May 17 '06 #3

P: n/a
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
May 17 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.