Linked Server Table Listing

Question

Post reply

Linked Server Table Listing

aflores 67795

Valued Member

Points: 60
More actions
February 18, 2010 at 6:23 pm

#141613

How can I get a table listing for a linked server?

I can query fine from it and do not have access to any sys.tables equivalent on the other side...

If I go to Server Objects, Linked Servers, Select My Linked Server, Maximize Catalogs, Then Maximize Default, Then Tables I can see them there ... but i want to access it through T-SQL so I can dynamically do a loop...

Your help is appricated!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

if you don't have access to sys.tables, then it's not a SQL Server (at least not a 2005 or 2008 server). What's the other side?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

aflores 67795 Valued Member Points: 60 More actions · Answer 2

aflores 67795

Valued Member

Points: 60

February 18, 2010 at 6:29 pm

#1121507

CA-DATACOM

aflores 67795 Valued Member Points: 60 More actions · Answer 3

aflores 67795

Valued Member

Points: 60

February 18, 2010 at 6:36 pm

#1121509

Sorry not sure if i made it clear on top... but I can do queries fine...

But I dont have the ability to get tables names from the other side.. I want to though... through sql

Lowell SSC Guru Points: 323482 More actions · Answer 4

no matter what the linked server type is, you can use this command:

EXEC dbo.sp_Tables_Ex myLinkedServer

that will list all the tables available in the linked server...whether sql,access,oracle,or whatever...i think it might auto generate the "right" command based on the server type.

the only time it gets a little wierd is when your linked server has access to more than one database...it does not iterate thru all possible values, just the default database you land in ie a linked server to another SQL server, as remote sa...you land in master, but could access other databases by way of linkedserver.dbname.dbo.sysobjects instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!