I'm having trouble getting the current database name when calling a stored procedure. My stored procedure drops tables in the database when the name of the table is a match. I do not want to have to pass the current database name into the stored procedure. The problem is happening where I'm calling the stored procedure to do this in a database that is different from the database that has the tables I want to drop.

I've tried using set @d_name = (SELECT DB_NAME()) within my stored procedure to get the name of the current database, but it keeps getting the name of the database the stored procedure is in. Any ideas?

Post your full select statement. you may need to select three part name in your select. (select objectname from database.dbo.objectname)

espin,

Yes, unless we see your full code, it is not easy to suggest any way.

As Far As I Understood your post, I dont think you can do it the way you are trying to.

Assume you are into db2 now and the stored proc is in db1

use db2
go

Execute db1.dbo.storedprocname
go

Still it would use the DB_NAME() as db1, as stored proc is an object of db1. May be replicating the SP to the db2 would solve your problem.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection