How to perform query in same table name but in multiple database

what is the best way for me to query table with same table name but located in multiple db's in the same server ?

Here is my current scripts which only extract only in table and one db.

select ColumnA, count(*) as count from TableTest44 with ( nolock )
where (((sid = 1 and eventid = 1) AND nname <> '')
AND (datetime >= '2010-08-01 00:00:00.000' and datetime <'2010-08-31 23:59:59.999'))
AND (username NOT LIKE 'samkry%'
motioneyeAsked:
Who is Participating?
 
Anurag AgarwalCommented:
hello you only need to give full details of table in this case like

Database_Name.Schema_Name.Table_name for refering a table in different database in same server
 for eg if u got TableTest44 in 3 different databse say DB1,DB2,DB3


select ColumnA, count(*) as count from DB1.dbo.TableTest44 with ( nolock )
where (((sid = 1 and eventid = 1) AND nname <> '')
AND (datetime >= '2010-08-01 00:00:00.000' and datetime <'2010-08-31 23:59:59.999'))
AND (username NOT LIKE 'samkry%'

select ColumnA, count(*) as count from DB2.dbo.TableTest44 with ( nolock )
where (((sid = 1 and eventid = 1) AND nname <> '')
AND (datetime >= '2010-08-01 00:00:00.000' and datetime <'2010-08-31 23:59:59.999'))
AND (username NOT LIKE 'samkry%'

select ColumnA, count(*) as count from DB3.dbo.TableTest44 with ( nolock )
where (((sid = 1 and eventid = 1) AND nname <> '')
AND (datetime >= '2010-08-01 00:00:00.000' and datetime <'2010-08-31 23:59:59.999'))
AND (username NOT LIKE 'samkry%'

only change in databse will come, normaly schema name is dbo but if you have change it thn use tht, and then your table name
0
 
Asim NazirCommented:
In simple words, you need to use fully qualified names including db name for querying DB. e.g.

select *
from dbo.ThisTable t1
join ThatDatabase.dbo.ThatTable t2
on t1.KeyColumn = t2.KeyColumn

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LowfatspreadCommented:
what are you trying to do?
  production , ad-hoc, known databases , or you have to determine database names? .... etc

how often do you intend to do it?
  do the results need to be consistent?
      can we run in parallel
         synchronously/asynchronously

...

some background please...

why is the data split like this?
how much data?

can you set up a cross database View?






0
 
reb73Commented:
You could also consider using the undocumented stored procedure 'sp_msforeachdb' to dynamically detect and run the query from multiple databases. This is useful if you have the table in quite a few databases..

Sample code is attached below, you will have to structure the global temp table (##qry1) to match the query results and change/adapt the actual query to get what you need.


set quoted_identifier off
if object_id('tempdb..##qry1','u') is not null
begin
	drop table ##qry1
end

create table ##qry1
(
	 DBName	sysname not null
	,Type	char(2) null
	,Cnt	int null
)

declare @sql varchar(1000)
set @sql = "
use ?
if object_id('sysobjects') is not null
begin
	insert  ##qry1 (DBName, Type, Cnt)
	select '?' as dbname, type, count(*) from sysobjects
	group by type
end 
"
exec sp_msforeachdb @sql

select * from ##qry1

drop table ##qry1
set quoted_identifier on

Open in new window

0
 
motioneyeAuthor Commented:
Tq, its partially help me to fix my problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.