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.
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
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.
When using Fosco's method, it is a good idea to put in brackets [] around the database name:
SELECT 'SELECT count(*) FROM ' + '[' + name + ']' + '.dbo.transactions'
FROM master..sysdatabases
WHERE name like 'Client%' and isnumeric(substring(name,6,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.
