Link Server Script

Question

Post reply

Link Server Script

mike-511834

SSC Enthusiast

Points: 166
More actions
May 14, 2008 at 8:01 am

#71949

I have used to sp_helpserver to get me listing of the link servers on my server; is there there a way to get a listing of tables on the linkserver by the link server name?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

How about

select * from [linkedServer].[dbName].sysobjects where xtype = 'u'

mike-511834 SSC Enthusiast Points: 166 More actions · Answer 2

I gave that a try but i received the following error invalid object name linksername.dbname.sysobjects

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 3

First off, is this a SQL Server linked server? If not, then sysobjects very well may not apply. If it is SQL Server, what version? If it's 2005 try this:

select name

from [LinkedServername].database.sys.objects

where type='u'

DAB

mike-511834 SSC Enthusiast Points: 166 More actions · Answer 4

thanks for the reply; not all the servers are SQL servers. I have an informix, and MySQL server. What i am trying to do is create a user drop list of linked servers (which i can do). What i want is when the user selects one of the linked servers that i can populate another drop list containing the listing of tables on that linked server. At run time the only information i can attain is the basic information provided from SQL (sp_helpserver) which only provides the link server name.

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 5

I don't know anything about Infromix, but for MySQL you can execute the following:

select * from openquery (MYSQL, ' select table_schema, table_name

from information_schema.TABLES; ' )

DAB

mike-511834 SSC Enthusiast Points: 166 More actions · Answer 6

Thank you very much for the quick response.

That provided the data that i wanted. I kicking myself for forgetting about using OPENQUERY to retrieve the information.

I really appreciate the help,

- Mike

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 7

Glad I could help. One more item. Beware if your MySQL instance is running on a *nix server. If that is the case you will need to take case into account as information_schema <> INFORMATION_SCHEMA, etc.

DAB

Sergiy SSC Guru Points: 110208 More actions · Answer 8

More universal script:

select *

from master.dbo.SYSREMOTE_TABLES (

'linked_server_name',

NULL,

NULL )

Works not only on SQL Server remote servers but any kind of remote servers, including ODBC connection to files using Jet.OLEDB driver.

_____________
Code for TallyGenerator

mike-511834 SSC Enthusiast Points: 166 More actions · Answer 9

mike-511834

SSC Enthusiast

Points: 166

May 15, 2008 at 8:47 am

#815930

Hi,

is there an equivalent query for SQL 2005?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

INFORMATION_SCHEMA is an ANSI standard, so it should work on most RDBMSes, although I've never tried it on other vendors' platforms. It certainly works on SQL 2000 and 2005.

John

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 11

I think he's asking for a 2005 equivalent of master.dbo.SYSREMOTE_TABLES

mike-511834 SSC Enthusiast Points: 166 More actions · Answer 12

Yes, i tried the query

select *

from master.dbo.SYSREMOTE_TABLES (

'LnkServerName',

NULL,

NULL )

and it works great on 2000 but on 2005 which we will be upgrading towards in the next few weeks it does not work.

i appreciate the help everyone is providing...

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

Yes, I think you're right. However, that appears to be an undocumented table or view in SQL 2000 that didn't survive to 2005. That's the danger of using undocumented features.

John

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 14

This solution should work for 2000 and 2005:

sp_catalogs ' '

DAB