Get data from different databases dynamically in sql server

I am facing a problem in sql query. I have my databases which has same tables and same column in every table. I want to retrieve same columns from two or three tables in every database. ex.

DatebaseName      TableName(exist in every database)
 
Demo               Patient, charge, WorkDescriptor
Medical            Patient, charge, WorkDescriptor
Test               Patient, charge, WorkDescriptor

This is my query

select 
db_name() as DBName,
substring(convert(varchar, c.eDate, 101), 0, 11) as Date,
c.operatorID as username,
count(wd.code) as TotalCPT,
p.acctnumber as TotalPatient,
sum(c.amount) as TotalAmount
from Charge c
inner join workDescriptor wd on wd.IDWorkDescriptor = c.WorkDescriptorID
inner join patient p on c.PatientID = p.IDPatient
where c.eDate between '01/01/2009' and '05/31/2011'
group by c.operatorID, substring(convert(varchar, c.eDate, 101), 0, 11), p.acctnumber
order by substring(convert(varchar, c.eDate, 101), 0, 11)
compute sum(count(wd.code))

It is retreive the data of current database. But I want the output data from every database. like example solution

DBName      Date(mm/dd/yyyy)Username  TotalCPT  TotalPatient TotalAmount
Demo           01/01/2010      A         1           1            41.82
Demo           01/01/2010      A         5           4            70.00
Demo           01/01/2010      B         3           3            41.84
Medical        01/02/2010      A         1           1             0.00
Medical        01/02/2010      B         5           4            50.24
Medical        01/02/2010      B         3           3            21.50    
Test           01/03/2010      A         1           1            56.98         
Test           01/03/2010      A         5    开发者_运维问答       4            45.75         
Test           01/03/2010      B         3           3            56.52    
                                       ------     --------       ---------------
                                        27           25          384.65

Any suggestion will be appreciated. If you have any query to achieve my goal. Thanks in advance

If absolutely the same query is supposed to be run against every databse, you could try something like this:

DECLARE @DBIndex int;

DECLARE @results TABLE (  -- the types are based on assumptions, should be reviewed
  DBName sysname,
  Date varchar(11),
  username varchar(50),
  TotalCPT int,
  TotalPatient int,
  TotalAmount decimal(10, 2)
);

SET @DBIndex = 0;

WHILE @DBIndex < 3 BEGIN
  SET @DBIndex = @DBIndex + 1;
  IF @DBIndex = 1
    USE Demo
  ELSE IF @DBIndex = 2
    USE Medical
  ELSE IF @DBIndex = 3
    USE Test;

  INSERT INTO @results
  select 
  db_name() as DBName,
  substring(convert(varchar, c.eDate, 101), 0, 11) as Date,
  c.operatorID as username,
  count(wd.code) as TotalCPT,
  p.acctnumber as TotalPatient,
  sum(c.amount) as TotalAmount
  from Charge c
  inner join workDescriptor wd on wd.IDWorkDescriptor = c.WorkDescriptorID
  inner join patient p on c.PatientID = p.IDPatient
  where c.eDate between '01/01/2009' and '05/31/2011'
  group by c.operatorID, substring(convert(varchar, c.eDate, 101), 0, 11), p.acctnumber
  order by substring(convert(varchar, c.eDate, 101), 0, 11)
  compute sum(count(wd.code))
END;

SELECT *
FROM @results
ORDER BY …

I would possibly remove the ORDER BY clause from the main query, because what ultimately matters is the ORDER BY in the final SELECT, i.e. from @results. Some additional columns might then need to be added to @results for them to be used in ORDER BY.

Very easy, use a union, 3 part naming, and hard code the db names; like so:

select 
'Demo' as DBName,
substring(convert(varchar, c.eDate, 101), 0, 11) as Date,
c.operatorID as username,
count(wd.code) as TotalCPT,
p.acctnumber as TotalPatient,
sum(c.amount) as TotalAmount
from Demo.dbo.Charge c
inner join Demo.dbo.workDescriptor wd on wd.IDWorkDescriptor = c.WorkDescriptorID
inner join Demo.dbo.patient p on c.PatientID = p.IDPatient
where c.eDate between '01/01/2009' and '05/31/2011'
group by c.operatorID, substring(convert(varchar, c.eDate, 101), 0, 11), p.acctnumber
order by substring(convert(varchar, c.eDate, 101), 0, 11)
compute sum(count(wd.code))
UNION ALL
select 
'Medical' as DBName,
substring(convert(varchar, c.eDate, 101), 0, 11) as Date,
c.operatorID as username,
count(wd.code) as TotalCPT,
p.acctnumber as TotalPatient,
sum(c.amount) as TotalAmount
from Medical.dbo.Charge c
inner join Medical.dbo.workDescriptor wd on wd.IDWorkDescriptor = c.WorkDescriptorID
inner join Medical.dbo.patient p on c.PatientID = p.IDPatient
where c.eDate between '01/01/2009' and '05/31/2011'
group by c.operatorID, substring(convert(varchar, c.eDate, 101), 0, 11), p.acctnumber
order by substring(convert(varchar, c.eDate, 101), 0, 11)
compute sum(count(wd.code))
UNION ALL
select 
'Test' as DBName,
substring(convert(varchar, c.eDate, 101), 0, 11) as Date,
c.operatorID as username,
count(wd.code) as TotalCPT,
p.acctnumber as TotalPatient,
sum(c.amount) as TotalAmount
from Test.dbo.Charge c
inner join Test.dbo.workDescriptor wd on wd.IDWorkDescriptor = c.WorkDescriptorID
inner join Test.dbo.patient p on c.PatientID = p.IDPatient
where c.eDate between '01/01/2009' and '05/31/2011'
group by c.operatorID, substring(convert(varchar, c.eDate, 101), 0, 11), p.acctnumber
order by substring(convert(varchar, c.eDate, 101), 0, 11)
compute sum(count(wd.code))

There are other ways to do it, but this one will work and is just as efficient as others that I can think of for now.

UPDATE BASED ON COMMENT

Ok, this should work, might need a bit of tweaking, but the idea is there:

CREATE TABLE #t1 (DBName VARCHAR(128), [Date] DATETIME, username VARCHAR(128), TotalCPT INT, TotalPatient INT, TotalAmount DECIMAL (16,2))


DECLARE @dbName VARCHAR(128), @sql VARCHAR(MAX), @fromDate DATETIME, @toDate DATETIME

SELECT @fromDate = '01/01/2009', @toDate = '05/31/2011'

DECLARE db_cursor CURSOR FOR 

SELECT name
FROM Master.sys.databases
WHERE name NOT IN ('master','tempdb','msdb','model')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @sql = '
INSERT INTO #t1(DBName, [Date], username, TotalCPT, TotalPatient, TotalAmount)
select ''' + @dbName + ''' as DBName,
substring(convert(varchar, c.eDate, 101), 0, 11) as Date,
c.operatorID as username,
count(wd.code) as TotalCPT,
p.acctnumber as TotalPatient,
sum(c.amount) as TotalAmount
from Demo.dbo.Charge c
inner join Demo.dbo.workDescriptor wd on wd.IDWorkDescriptor = c.WorkDescriptorID
inner join Demo.dbo.patient p on c.PatientID = p.IDPatient
where c.eDate between ''' + @fromDate + ''' and ''' + @toDate + '''
group by c.operatorID, substring(convert(varchar, c.eDate, 101), 0, 11), p.acctnumber
order by substring(convert(varchar, c.eDate, 101), 0, 11)
compute sum(count(wd.code))'

EXEC (@sql)

FETCH NEXT FROM db_cursor INTO @dbName

END
CLOSE db_cursor
DEALLOCATE db_cursor


SELECT * FROM #t1

DROP TABLE #t1