Link Server Script

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Link Server Script

17 posts, Page 1 of 2

1 2 »»

Link Server Script

Rate Topic

Display Mode

Topic Options

Author

Message

mike-511834

Posted Wednesday, May 14, 2008 8:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2008 10:19 AM
Points: 8, Visits: 21

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?

Post #500513

SQL ORACLE

Posted Wednesday, May 14, 2008 8:23 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 05, 2013 4:43 PM
Points: 1,473, Visits: 1,314

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

Post #500548

mike-511834

Posted Wednesday, May 14, 2008 8:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2008 10:19 AM
Points: 8, Visits: 21

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

Post #500562

Scalability Doug

Posted Wednesday, May 14, 2008 9:06 AM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 05, 2012 12:43 PM
Points: 655, Visits: 6,097

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

Post #500592

mike-511834

Posted Wednesday, May 14, 2008 9:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2008 10:19 AM
Points: 8, Visits: 21

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.

Post #500608

Scalability Doug

Posted Wednesday, May 14, 2008 9:26 AM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 05, 2012 12:43 PM
Points: 655, Visits: 6,097

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

Post #500617

mike-511834

Posted Wednesday, May 14, 2008 9:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2008 10:19 AM
Points: 8, Visits: 21

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

Post #500636

Scalability Doug

Posted Wednesday, May 14, 2008 9:41 AM

SSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 05, 2012 12:43 PM
Points: 655, Visits: 6,097

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

Post #500645

Sergiy

Posted Wednesday, May 14, 2008 6:33 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:22 PM
Points: 4,557, Visits: 8,234

More universal script:

select *
from master.dbo.SYSREMOTE_TABLES (
		'linked_server_name',
		NULL,
		NULL,
		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.

Post #500953

mike-511834

Posted Thursday, May 15, 2008 8:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2008 10:19 AM
Points: 8, Visits: 21

Hi,

is there an equivalent query for SQL 2005?

Post #501376

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions