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