CODEWITHSUNDEEP
sql-server
Amr Elnashar
Amr Elnashar

Reputation: 1769

Query on multiple databases (SQL server)

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.

Upvotes: 11

Views: 16682

Answers (4)

Stephan Moskhalis
Stephan Moskhalis

Reputation: 11

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))

Upvotes: 1

Fosco
Fosco

Reputation: 38506

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.

Upvotes: 1

Denis Valeev
Denis Valeev

Reputation: 6015

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

Upvotes: 12

CyberDude
CyberDude

Reputation: 8949

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.

Upvotes: 0

Related Questions