Query on multiple databases (SQL server)

Question

I have multi databases with same structure its name like that "Client1234" the different in numbers beside "client" i have table called "Transactions" inside each database and i want to run query to get count all raws in "transactions" table in all databases.

also when i select database i need to check it has the client word and it has numbers beside the word.

Answer 1

If the name and number of the databases you wish to query is not known beforehand then you can only do this by using a dynamic query. You'll need to generate a script like

SELECT COUNT(*) FROM Client1.dbo.Transactions
SELECT COUNT(*) FROM Client2.dbo.Transactions
...

Of course you need to have your appropriate permissions in place for each database.

Answer 2

Try to use sp_msforeachdb stored procedure like so:

create table #temp ([rows] int, [client] varchar(100))
exec sp_msforeachdb '
if ''?'' like ''Client%'' and exists(select * from ?.sys.tables t where t.name = ''Transactions'')
begin
insert into #temp select count(*), ''?'' from ?..Transactions
end
'
select * from #temp 
drop table #temp

Answer 3

You can use dynamic SQL to create these queries:

select 'select count(*) from ' + name + '.dbo.transactions'
from master..sysdatabases
where name like 'Client%'
 and isnumeric(substring(name,6,1))

This would return a result set with each row being a SQL query to count a specific database. It could be consumed by a programming language, used as a cursor, etc.. If you provide more detail I may be able to provide a better example.