Selecting from multiple databases 1

[awaywifye]

To set things up:
I have a server with ~23 databases. 14 use a standard naming convention aaa_001, aaa_002, aaa_003, etc...

Is there an EASY WAY to pull data from all 14 of these using one select statement (ie- SELECT * from aaa_*..table1)

I currently write a select statement for database aaa_001, UNION ALL and do the same select statement for aaa_002, etc. Looks like:

SELECT DISTINCT
table1.chickenname
FROM aaa_001..table1
UNION ALL
SELECT DISTINCT
table1.chickenname
FROM aaa_002..table1
UNION ALL
SELECT DISTINCT
table1.chickenname
FROM aaa_003..table1

I have even begun playing with DTS and spider webbing to each database. Select data from aaa_001.table1, dump to warehouse.table1, upon completion, elect data from aaa_002.table1, dump to warehouse.table1, upon completion, etc...

 

[vongrunt]

> Is there an EASY WAY to pull data from all 14 of these using one select statement (ie- SELECT * from aaa_*..table1)

Yes. This method is known as "copy & paste"

Depending on purpose, you may find sp_MSForEachDB procedure useful.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

I don't know if this will help...Just one way of doing it without too much hardcoding..

Sometimes I will scipt storedprocs this way. Just reroute the @select to a print or select statment. Hit all tables or all indexes or all databases or servers or whatever.

Declare @dbname varchar(3000),@Select varchar(8000)
set @dbname =''
set @Select =''
while not @dbname is null
begin
select @dbname = min(name) from master..sysdatabases where name>@dbname and not name like ('reportse%')
if @dbname is null
begin
break
end
set @select = @select + 'Select * from ' + @dbname + '.dbo.sysobjects where type =''u''  union all '
end
set @select = left(@select,len(@select)-10)

exec (@Select)
select @select
print  @select

 

[awaywifye]

I looked at using sp_MSForEachDB but it appears to hit all databases.

Using NoCoolHandle's approach, I took my select and turned it into a stored procedure.

Declare
@dbname varchar (7)

select distinct
tb1.chickenname
from '@dbname'..table1 tb1

Then using DTS I wrote a quick cursor that empties out the "temp" table, feeds each name from master.sysdatabases like 'aaa_%' into the stored procedure and dumps the result into the "temp" table.