Linked Server Table Listing

Question

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

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: 323518 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!

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

YES!!!!!

Thank you sir. You have made my life easier... Appreciate it.

David Rueter SSCrazy Points: 2661 More actions · Answer 6

Thanks! sp_Tables_Ex is just what I was looking for.

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 7

This may also help :

SELECT * FROM [LinkedServer].[Database].sys.tables

Is_Linked might have to be 1 in sys.servers on the server you are quering from.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort