471,092 Members | 1,941 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Displaying database name within select within UNION

I have the following stored procedure in SQL 2000 and would like to
diplay the database name where data is drawn from. I'm using 4
databases db1, db2, db3, db4 which all have the same table (Table1)
with identical column names (Surname, GivenNames).

CREATE PROCEDURE [dbo].[x_searchwildcard] @varSurname VARChar(25)
AS
Select a.Surname, a.GivenNames
From [db1]..Table1 As a
Where a.Surname LIKE @varSurname + '%'
UNION
Select a.Surname, a.GivenNames
From [db2]..Table1 As a
Where a.Surname LIKE @varSurname + '%'
UNION
Select a.Surname, a.GivenNames
From [db3]..Table1 As a
Where a.Surname LIKE @varSurname + '%'
UNION
Select a.Surname, a.GivenNames
From [db4]..Table1 As a
Where a.Surname LIKE @varSurname + '%'
Order By a.Surname,a.GivenNames
GO

I tried the following
Select a.Surname, a.GivenNames, db_name()
However it only gave me the name of the database where the stored
procedure is kept (in my case 'Common')

I was hoping it would display results something like the following

Surname GivenNames Database
------- ---------- --------
Fred Smith db1
Freddy Smith db2
Fred Smith db3
Fred Smithe db3
Fred Smith db4
Fred Smithye db4

Instead I receive

Surname GivenNames Database
------- ---------- --------
Fred Smith common
Freddy Smith common
Fred Smith common
Fred Smithe common
Fred Smith common
Fred Smithye common
Any ideas?

Thanks
Rick

Jul 23 '05 #1
1 1693
Select a.Surname, a.GivenNames, 'db1' as 'Database'
From [db1]..Table1 As a
....
Select a.Surname, a.GivenNames, 'db2'
From [db2]..Table1 As a

Simon

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by DarthMacgyver | last post: by
29 posts views Thread by Scott Marquardt | last post: by
8 posts views Thread by Greg Lyles | last post: by
11 posts views Thread by dba | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.