Query accross multiple databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query accross multiple databases

Author

Topic

joefdev
Starting Member

1 Post

Posted - 2004-12-15 : 13:58:32

Two (related) questions:

1. I have a query that spans multiple databases to give me a list of users:

select user_id, full_name from accounting.dbo.user_table
where allow_login = 'y' union
select user_id, full_name from humanresources.dbo.user_table
where allow_login = 'y' union
select user_id, full_name from legal.dbo.user_table
where allow_login = 'Y' union
select user_id, full_name from testlib.dbo.user_table
where allow_login = 'Y'
order by full_name

This returns a dataset that has a userId and the name. But if I need to know what database these users are a member of I run a seperate query for each user to get that information:
...
SELECT 'humanresources' as 'db', user_id, full_name
FROM humanresources.dbo.user_table
where allow_login = 'Y'and user_id like @userName
UNION

SELECT 'accounting' as 'db', user_id, full_name
FROM accounting.dbo.user_table
where allow_login = 'Y'and user_id like @userName UNION
(etc)...

Is there a query that will return one row per unique Id, along with a listing of database that ID was found in?

ie


userid     fullname                databases
JOE        Joseph Schmoseph        Legal, humanresources
TED        Ted Schmed              Legal, accounting
FRED       Fred Shred              Accounting, testlib

2. As we add more departments through the application, new databases are created(this is the way the application does it, we have no control over it). Is there a way write the query so that when it is run it will grab the information out of all available databases for that application? My goal is to make the query a stored proc that can be called from a different application, and I don't want to have to worry about editing the sp each time a database is added or removed.

thanks,
Joe

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-15 : 14:12:02

You can get the databases from master..sysdatabases but will have to code a loop for each database and use dynamic sql inserting into a temp table to get the data

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-15 : 14:20:29

Something like this

create table #t (uid int, name varchar(50))
declare @sql nvarchar(2000)
declare @i int
declare @db varchar(128)
select @db = ''
while @db < (select max(name) from master..sysdatabases)
begin
select @db = min(name) from master..sysdatabases where name > @db
select @i = null
select @sql = 'select @i = 1 where exists (select * from ' + @db + '..sysobjects where name = ''user_table'')'
exec sp_executesql @sql, N'@i int out', @i out
if @i is not null
begin
select @sql = 'select user_id, full_name from ' + @db + '.dbo.user_table where allow_login = ''y'''
insert #t exec (@sql)
end
end

select distinct * from #t
order by name

drop table #t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources