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

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