May 14, 2008 at 12:43 pm
Hi,
I have multiple SQL2000 databases (one for each customer) that share the same basic structures, functions and stored procedures. I try to write generic code that may be shared by all.
I have a need to identify the database name for the database where a stored procedure is run, but I cannot find any way to identify the DB name. Ideally I'd be looking for an @@ variable, but none exists that I can see.
Any help?
Thanks,
Chuck Hardy
May 14, 2008 at 2:19 pm
Since you are using SQL 2000, not much help there. But, you could try something like this using a combination of @@SPID in conjunction with the sp_who system proc.
create proc getDB
as
declare @mysp_who table
( spid int,
ecid int,
status varchar(20),
loginname varchar(50),
hostname varchar(50),
blk varchar(4),
dbname varchar(128),
cmd varchar(128),
requestid int
)
insert into @mysp_who
exec sp_who
select dbname from @mysp_who where spid = @@spid
go
exec dbo.getDB
dab
May 14, 2008 at 3:38 pm
how about
select db_name() --returns dbname of current database
or db_name(dbid) will return dbname for that dbid
see BOL
---------------------------------------------------------------------
May 14, 2008 at 4:46 pm
I thought about that but for some reason i had the impression that it took a db id as a parameter as in db_name( id ). My fault for not double checking.
DAB
May 15, 2008 at 3:01 am
Thanks to all! Select db_name() works great. My searches in BOL using 'database name' never turned it up.
Chuck
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply