how to search from all user 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

SQL Server Development (2000)

how to search from all user databases...

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2004-04-28 : 15:42:07

in a server?

PeterG
Posting Yak Master

156 Posts

Posted - 2004-04-28 : 15:43:27

Let me rephrase the question:

How do I search from one field in a table that exists in multiple databases?

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-28 : 15:43:53

Man that's terse

Do you mean for? Not From.

Brett

8-)

PeterG
Posting Yak Master

156 Posts

Posted - 2004-04-28 : 15:46:26

I want to search for a value in a field from a table that is in multiple dbs.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-28 : 15:47:33


USE Northwind
GO

    SELECT * 
      FROM Employees e1
 FULL JOIN pubs.dbo.Employee e2
	ON e1.LastName = e2.lname

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-29 : 05:51:19

Do you have a limited number of databases? If yes, then you could use the UNION statement to do this. example:


SELECT colA, colB, colC FROM db1.dbo.tablename WHERE ID = @ID
UNION ALL
SELECT colA, colB, colC FROM db2.dbo.tablename WHERE ID = @ID
UNION ALL
SELECT colA, colB, colC FROM db3.dbo.tablename WHERE ID = @ID
UNION ALL...

If you will have a variable number of databases they you probably need to generate the UNION statements on-the-fly, using the sysdatabases table and dynamic SQL, but I wouldn't want to go down that route.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources