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
Coordial wishes from joseph.
Thanks a lot
your code helped me
pls help me to get all database names in a sql server
Thanks in advance
Hi…
can i get Current Selected Database name in Variable and that variable can be used with USE [] command?
like
DECLARE @DBName sysname
SET @DBName = DB_NAME()
USE [@DBName]
It gives below error:
Could not locate entry in sysdatabases for database ‘@DBName’. No entry found with that name. Make sure that the name is entered correctly.
Padma,
You can get list of the tables from a database with folowing query
select name from sysobjects where type=’U’
here
sysobjects is a system table containg all information of the objects ina database like tables,vies,SP etc..
type-> User Defined Table..
if you use type=’P’ you will get all procedures …
also try this .. select * from sysobjetcs
Milan you can use current databse name in a script..
try like this..
declare @DBName varchar(50)
set @DBNAme=(select db_name())
use @DBName
go
Hi Vishnu,
I have the same problem, I wanted to use current database name in a variable and then use that variable in USE command. I tried your commands but it didn’t work, I got an error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘@DBName’.
What would be the reason, or is there any other way to do that.
I want to run a script in all the databases in a server.
Thanks.
Hello Shoaib,
You can use sp_msforeachdb stored procedure. Everywhere in script “?” (question mark) will be replaced with database name and the script will be executed for each database. For example to perform sp_helpdb for each database:
sp_msforeachdb ‘sp_helpdb ?’
Regards,
Pinal Dave
Hi Pinal,
Thanks for the response.
However, I am still not able to run the script against all the database.
Because, I have a big script, not just a stored procedure like SP_HELPDB you used.
e.g. if I run: select name from sys.sysusers statement in every database INDIVIDUALLY, it would give different numbers, but when i run
sp_msforeachdb ‘select name from sys.sysusers ?’
it gives the numbers from current database * total number of databases.
Like if I use Master, the result is 30 and total databases on the server are 38 so total rows 1140. also same result for every database.
I hope you understand my question.
I want to use USE command (or any other way to change the database name in db_name) then run my big script.
Thanks for your time.
Joseph !!
You can get all DB names in a current server by using following query…. it is pretty usefull while writing some SQL scripts or creating Procedures…
select name from master..sysdatabases
thank you..
You’re the man!
You help me
Thanks again for your helpful site. Keep up the good work!
Thanks a lot.
Great … thanks I´ve been looking for this. I needed a script that would change settings for the current database… did it this way:
Declare @DBname nvarchar(50)
SELECT @DBname = DB_NAME()
exec (‘ALTER DATABASE ‘ + @DBname + ‘ SET TRUSTWORTHY ON’)
exec (‘ALTER AUTHORIZATION on DATABASE::’ + @DBname + ‘ to sa’)
what if i want to display all database names on the server
how do i get all the names of databases i have in a list box control in a windows form !!!
how to get all database name from a sql server without run sql script, but use some command or utility like
“osql -L > .\server.list”
hey how can i display all database name
can any one tell the query
@navin
Select name from sys.databases — SQL Server 2005 and above
select name from sysdatabases — SQL Server 2000
~IM
or
EXEC sp_databases
yeah very useful
anyway
“as database”
is not essential
How do we get this work?
The first set of query to get MNO_V12 database will be replaced by a function in ABC_V15 database.
Reason we need this query is that MNO_V12 is not controlled by us and we need a single place to make the switch.
—-
Use Master
Go
declare @db_name as sysname
select @db_name= name from sysdatabases
where name = ‘MNO_V12’
Use ABC_V15
Go
SELECT c.CustomerName
FROM [@db_name].dbo.tbl_customers c
where c.customerid = 70
—-
Thanks
Kartik
Grande Givanildo!!!!