sql - How to select from unknown number of databases? -

sql - How to select from unknown number of databases? -

i want show client history of total orders across multiple 'vendors'. each vendor has separate database in sql server store own orders.

in database know vendors user signed with. sequence needs go this:

get vendorids user signed with. go vendor table , server + database name perform select statement gets orders each order table in each of vendor databases user signed to. declare @userid int = 999 select count(ordernumber) 'orders' --- need kind of loop here? [vendorserver].[vendordb].[ordertable] o1 o1.userid = @userid

how aggregate of total number of orders client made when orders spread across multiple databases?

the user may signed on 100 vendors. has query across 100 databases. extreme illustration possible.

this can solved using dynamic query: query generated dynamically , executed.

without table schema it's impossible write work in environment thought be

declare @query nvarchar(max) = '' select @query += 'union select whatever ' + vendorserver + '.' + vendordb + '.ordetable condition' vendor vendorid in (all vendorids user signed with) set @query = substring(@query, 10, len(@query)) exec sp_executesql(@query)

the op in comment described schema

create table user_vendor ( userid int , vendorid int ) create table vendors ( vendorid int , name varchar(50) , databasename varchar(50) , servername varchar(50) )

in case query/stored procedure body be

declare @userid int = '999' declare @query nvarchar(max) = '' declare @vuserid nvarchar(10) = cast(userid nvarchar(10)) select @query += 'union select count(ordernumber) [orders] ' + v.servername + '.' + v.databasename + '.ordetable o1.userid = ' + @userid + ' ' user_vendor uv inner bring together vendors v on uv.vendorid = v.vendorid uv.userid = @userid set @query = substring(@query, 10, len(@query)) exec sp_executesql(@query)

sqlfiddle demo select @query instead of exec sp_executesql(@query)

the added variable @vuserid avoid multiple cast in query, user table not needed in query.

to total figure of orders, instead of count every vendor, line

set @query = substring(@query, 10, len(@query))

should changed to

set @query = 'select sum([orders]) [orders] (' + substring(@query, 10, len(@query)) + ') a'

sql sql-server