We help IT Professionals succeed at work.

T-SQL: get total records from multiple databases

I have 100 databases with the same structure and I want to count all records on one of the tables (same table in all 100 databases)

I'm getting unrealistic results using UNION... is there a better way? Iam using something like this:

select count(*) from database001.dbo.Projects
union
select count(*) from database002.dbo.Projects
union
select count(*) from database003.dbo.Projects
union
select count(*) from database004.dbo.Projects

and the results are something like this:
1
7
8
17

where it should be (not accumulated):
186
282
1375
47
Comment
Watch Question

So if you execute the following two statements separately, you get the correct answer in each case?

select count(*) from database001.dbo.Projects

select count(*) from database002.dbo.Projects

but linking them with a union gives you a wrong result?

The union should not make any difference.     What are you using to execute the query? Is there any formatting applied to the result set?
Top Expert 2011

Commented:
T-SQL should work as expected, can you please try these changes:

1. What is the result if you execute the statements individually instead of UNION
   i.e. try executing this statement
   select count(*) from database001.dbo.Projects

2. instead of * in your select statement replace with primaryId or UniqueId
    select count(PRIMARYID) from database001.dbo.Projects
    union
     select count(PRIMARYID) from database002.dbo.Projects

Author

Commented:
with a UNION I get the results in a way I can copy all the totals in one column and export to excel... without the UNION I cannot do that... alternatively, if there's a way I can SUMmarize these counts, that would work... al I need is a grand total...
Chris MangusDatabase Administrator
CERTIFIED EXPERT

Commented:
You could try this:

Exec sp_msforeachdb 'select count(*), ''?'' as dbname from ?.dbo.Projects'
For a grand total, the following should work....



Select sum (project_count) from
(
select count(*) as project_count from database001.dbo.Projects
union
select count(*) from database002.dbo.Projects
union
select count(*) from database003.dbo.Projects
union
select count(*) from database004.dbo.Projects
) Q
Chris MangusDatabase Administrator
CERTIFIED EXPERT
Commented:
Modified code which summarizes:

Create table #t (myCount int, database_name varchar(100))
Exec sp_msforeachdb 'Insert into #t select count(*), ''?'' as dbname from ?.dbo.Projects'
Select Sum(myCount) From #t
Drop table #t

Explore More ContentExplore courses, solutions, and other research materials related to this topic.