How to query multiple DBs with teh same name on separate servers?

By monzonc ·
I have multiple servers where I have this app - the app creates the DBs and tables. The DB and tables are call the same on all of the servers.. How can I query the DBs and tables on all of the server with a single query ? The server themselves have unique names.

For example I want to search for a trading partner with the ID of 1234567 on all servers under the same table name..


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Logically, a query can only do one thing at a time

by robo_dev In reply to How to query multiple DBs ...

Do you mean to re-use the same query on each database instance?

Then the way to do that would be to create a variable in the query that is populated with a table containing your list of database instances. Your code would then need to iterate through the process of sequentially retrieving the variable name, executing the query, then moving on to the next server.

You could, of course, hard-code the database instance names into the query, but then the query breaks if a server is renamed, and some error recovery code needs to be added so the whole query does not fail if a server were offline.

Collapse -

Reponse To Answer

by monzonc In reply to Logically, a query can on ...

Thank you robo..

Collapse -


by Tony Hopkinson In reply to How to query multiple DBs ...

You ned to add all the servers in as linked servers

Then it's just Select * From LinkedServerName.DatabaseName.SchemaName.TableName etc.

As for querying them all in one query that's a bit more complicated.

If they are static links to can hard code it e.g.

Select * From LinkedServer1.DatabaseName.SchemaName.TableName
Select * From LinkedServer2.DatabaseName.SchemaName.TableName
Select * From LinkedServer9.DatabaseName.SchemaName.TableName

But that's not what you call future proof...

After that you are into cursors on system metatables and executing dynamic sql and lots of clever stuff.

Some sort of replication mechanism back to a central reporting database might be a better option if this is more than an a one off request.

Collapse -

Reponse To Answer

by monzonc In reply to SQLServer?

Thanks for the reply back..

Related Discussions

Related Forums