How to list all the objects of another server

Question

How to list all the objects of another server

reggae_blur

Old Hand

Points: 395
More actions
November 9, 2010 at 5:51 am

#228830

Hi all.
Is it possible to list all the objects of another server by calling it in a separate server? In my case, I have 2 servers (say Server A and Server B). I want to get all objects of Server B by calling it in Server A. It should be like SELECT * FROM ServerB.sysobjects. Thanks!
MS SQL Version: 2000

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

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

reggae_blur (11/9/2010)
Hi all.
Is it possible to list all the objects of another server by calling it in a separate server? In my case, I have 2 servers (say Server A and Server B). I want to get all objects of Server B by calling it in Server A. It should be like SELECT * FROM ServerB.sysobjects. Thanks!
MS SQL Version: 2000

Create a linked server use the fully qualified name.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

sejal p gudhka SSCrazy Points: 2102 More actions · Answer 2

Yes it is very much possible if you can create linked server.

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

The first thing to do is to set up a linked server object on one server, pointing at the other. The next thing to do is loop through all the databases on the second server, and select from sysobjects on each one.

If you could provide a bit more detail on what you want to do and why, we could probably help you with a better solution.

John

reggae_blur Old Hand Points: 395 More actions · Answer 4

thanks for the immediate response! 🙂

actually we're not allowed to create a linked server. 🙁 but i can execute a query successfully in Server A. This query runs successfully when being ran in Server A: SELECT * FROM ServerB.dbo.Table1

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 5

Hi

If you are not allowed to have link server then why do you want to query in serverB

You have to create link server between Server A and ServerB inorder to query

Try with Syntax for Linked server

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]

[ , [ @provider= ] 'provider_name' ]

[ , [ @datasrc= ] 'data_source' ]

[ , [ @location= ] 'location' ]

[ , [ @provstr= ] 'provider_string' ]

[ , [ @catalog= ] 'catalog' ]

On scuessful you can use

SELECT * FROM ServerB.sysobjects

Thanks

Parthi

Thanks
Parthi

reggae_blur Old Hand Points: 395 More actions · Answer 6

Hi parthi,

Upon checking, we already have a linked server for Server B, that's why I can query successfully from Server B. My apologies.

So, my question now is, how can I get all the databases of Server B by executing it in Server A? Thanks!

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

reggae_blur (11/9/2010)
Hi parthi,
Upon checking, we already have a linked server for Server B, that's why I can query successfully from Server B. My apologies.
So, my question now is, how can I get all the databases of Server B by executing it in Server A? Thanks!

Try to write a script ?

Use some logic(use a loop & openquery...)

There is number of ways to get this.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

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

reggae_blur (11/9/2010)
So, my question now is, how can I get all the databases of Server B by executing it in Server A? Thanks!

You can't, unless you create a linked server object on Server A.

Another thing you might consider is using an Integration Services package.

John

reggae_blur Old Hand Points: 395 More actions · Answer 9

Parthi,

Running the query below in Server A gives me an "Invalid Object Name" error...:-)

SELECT * FROM ServerB.sysobjects

reggae_blur Old Hand Points: 395 More actions · Answer 10

I already have a linked server. I just don't know how the list of Server B's databases. 🙂

Executing sp_databases in Server A returns only the databases of Server A.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 11

reggae_blur (11/9/2010)
Parthi,
Running the query below in Server A gives me an "Invalid Object Name" error...:-)
SELECT * FROM ServerB.sysobjects

Try this

SELECT * FROM [ServerB].Tempdb.dbo.sysobjects

Thanks

Parthi

Thanks
Parthi

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 12

reggae_blur (11/9/2010)
Parthi,
Running the query below in Server A gives me an "Invalid Object Name" error...:-)
SELECT * FROM ServerB.sysobjects

Did you read my prrvious replay

FQN-- select * from [LSname].[dbname].[schema].[object]

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

reggae_blur Old Hand Points: 395 More actions · Answer 13

thanks muthukkumaran..

i tried this SELECT * FROM ServerB.master.dbo.sysdatabases

it worked! 🙂

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 14

reggae_blur (11/9/2010)
thanks muthukkumaran..
i tried this SELECT * FROM ServerB.master.dbo.sysdatabases
it worked! 🙂

Ur Welcome

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/