Microsoft SQL Server forum - Displaying database name within select within UNION

Author

Displaying database name within select within UNION

Rick

2005-05-27, 7:23 am

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].& #91;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

Simon Hayes

2005-05-27, 7:23 am

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

Sponsored Links