Yesterday while I was writing script for SQL SERVER – 2005 – Find Unused Indexes of Current Database . I realized that I needed SELECT statement where I get the name of the current Database. It was very simple script.
SELECT DB_NAME() AS DataBaseName
It will give you the name the database you are running using while running the query.
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
113 Comments. Leave new
1) I have 2 db one is parent db and another one is child db.
2) suppose while using child db i need parent table information normall we are using parent..tablename.
3) statically we declare @db=parent at that time we use exec() the query.
for ex:
declare @DB varchar(20)
set @db= parent
exec
(
‘select * from child inner join
(
SELECT distinct ia_grn_num FROM ‘+@DB+’..invtb_adjust where ia_stock_num = ”TYREI”
) b on ir_grn_num = b.ia_grn_num’
)
4) I want to use set @db= parent but i dont want to use exec() the query. Is it posible to sql server?
Hi Dave, similar question: I have multiple DBs with similar Schemas (for different levels of development). I need a way to dynamically specify which of these schemas is the one I want.
Kind of like a: “USE [Ask me for which DB and insert it here]” statement at the top of the script.
Any ideas?
-Ralph
Thank you very much Pinal.
Dear Sir,
Can please advise “How to find database name using table name in sql”
very useful thanks
Tks!!! Tks
Great post. Another one is very useful is:
select @@servername to get the server’s name.
MSSQL – How do you find which db the query is running.
i am aware of db_name()
query is :
select dbname1 from dbname1.table1
union all
select dbname2 from dbname2.table2
here i am using union to query two dbs.
i do i get dbname1, dbname2 in output column automatically.
in this case i cannot use db_name()
thank you sir,
i have one task..,
how can i get databases having same login,Password
Hello. I’m hoping for a solution to an issue I have.
There seems to be an explicit name set in a stored procedure, to a database that I had renamed for the sake of doing lab work.
The original name of the database was FOO1, and I restored it as FOO2. I’m using a tool in learning how to reverse engineer a database when I noticed that I have quite a few errors because of a reference in a Stored Procedure that references FOO1 explicitly.
The error I’m getting is:
Unable to resolve object ‘FOO1’.[myTable] in unknown ‘[MyServer].[FOO1]’ referenced by stored procedure ‘[MyServer].[FOO2].dbo.[myStoredProcedure]’
So I understand the error it’s looking for something in what was the original name of the database.
I found the line in the stored procedure that makes this reference:
FROM FOO1.dbo.Usertable p, ….
Is there a way (in a stored procedure) to automagically get database name, set it as a variable for example as, @DBNAME, so that I can change it to look like:
FROM @DBNAME.dbo.Usertable p …
Could someone please provide insight :)
Greatly appreciated.
thank u..
I have 3 databases in one server named A, B,C. One procedure sp_1 is in B. I will call that procedure from either A or C.According to calling database (i.e A or C) I have to set @Calling_Db_name which is in Procedure sp_1.
How I can do this.
Please help me..
How can you use the current database name to populate a field. The login default database in sql server query Cheers Nick
Can you please explain a bit more on your requirement Nick? DB_Name() would give you the value and it can be used with INSERT / UPDATE statement for you. So let me know.
How to search by database name within SQL server management studio because I have over 3,000 database attached to each instance
Can you provide more details? have you tried using Object Explorer Details window? There is a search option there
Good afternoon Pinal
May you help me telling me how to pass db_name() as default paramenter in a user defined function in sql server 2008, something like this:
CREATE FUNCTION [dbo].[test](@p_e1 AS [SYSNAME] = @@SERVERNAME, @p_e2 AS [SYSNAME] = DB_NAME())
RETURNS [BIT]
AS
BEGIN
IF @p_e2 = N’Some Data Base’
BEGIN
RETURN 1;
END
ELSE
BEGIN
RETURN 0;
END;
END
GO
Thanks a lot for your valuable help.
Thank you very much. Great.
Is there a way to find the database name or ID from within a stored procedure? DB_NAME() always returns the name of the database that the procedure is defined in. I want to know the name or ID of the database that the stored procedure was called from. Is this possible?
For example, If I have a procedure called MY_PROC, defined in MAIN_DB, that displays a database name,
USE DATABASE_A
GO
EXEC MAIN_DB.dbo.MY_PROC — Should Return ‘DATABASE_A’
USE DATABASE_B
GO
EXEC MAIN_DB.dbo.MY_PROC — Should Return ‘DATABASE_B’
Thank You,
Robbie