Query and compare INFORMATION_SCHEMA across servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Query and compare INFORMATION_SCHEMA across servers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-26 : 07:01:41

Phil writes "I'd like to compare INFORMATION_SCHEMA for a database in Server1 with the INFORMATION_SCHEMA for a database in Server2 (They're dev and production boxes.) Server1 has a link server established to server2. However, the only schema I can query on server2 from server1 is for the MASTER db. That is, I can issue

SELECT *
FROM server2.master.information_schema.columns

and get the correct result (but only for objects with catalog_name 'master').

Whenever I try to query the schema for another DB, I get the following error:

7314
OLE DB provider 'server2' does not contain table '"mydb"."information_schema"."columns"'.

No combination of n-part naming convention seems to be able to get around this. It works great on a loop-back, but not on the link to the other server.

Any ideas? I will accept any answer which involves programming in T-SQL, using INFORMATION_SCHEMA views (NOT system tables). I will ignore any answer with the words "red-gate", "third-party", or any suggestion to purchase such a solution. I'm guessing that I need to write an SP that creates a temp table from the results of the INFORMATION_SCHEMA query and then includes the temp table in a distributed join query. Or something like that.

Thanks for your time."

Kristen
Test

22859 Posts

Posted - 2004-05-26 : 07:15:34

This doesn't doesn't use the INFORMATION_SCHEMA, and it compares the data rather than the schema [well it does a bit, but its more for data comparison], but the good news is it does not include the words "red-gate" or "third-party"! However, it may give you some ideas.

http://www.sql-server-performance.com/vg_database_comparison_sp.asp

I wish I wish I wish that Microsoft put a GUID on syscolumns so that INSERTS and DELETIONS, at the same time as RENAME, were more easily detectable after-the-fact.

Kristen

philh
Starting Member

18 Posts

Posted - 2004-06-04 : 14:01:15

Thanks for the post, Kristen, but it doesn't fit the requirements.

Any other takers?

Phil Hegedusich
Senior Web Developer
IIMAK
http://www.iimak.com
My views are not necessarily my own.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources