Quite often a simple things makes experienced DBA to look for simple thing. Here are few things which I used to get confused couple of years ago. Now I know it well and have no issue but recently I see one of the DBA getting confused when looking at the DBID from one of the DMV and not able to related that directly to Database Name.
-- Get Current DatabaseID SELECT DB_ID() DatabaseID; -- Get Current DatabaseName SELECT DB_NAME() DatabaseName; -- Get DatabaseName from DatabaseID SELECT DB_NAME(4) DatabaseName; -- Get DatabaseID from DatabaseName SELECT DB_ID('tempdb') DatabaseID; -- Get all DatabaseName and DBID SELECT name,database_id FROM sys.databases;
Reference: Pinal Dave (https://blog.sqlauthority.com)
I am an ardent fan of you for your commitment to SQL Server knowledge sharing forums.
Its a very interesting tip.
I am new to Database Field and i am thinking to start my career as SQL Developer and DBA. Can anyone help me out about the syllabus for the certifications for MCDBA so that i can prepare. I shall be highly grateful for any kind of help. Please mail the syllabus on
However, the column alias names for these two are swapped with each other:
— Get DatabaseName from DatabaseID
SELECT DB_NAME(4) DatabaseID;
— Get DatabaseID from DatabaseName
SELECT DB_ID(‘tempdb’) DatabaseName;
Should be — Get DatabaseName from DatabaseID
SELECT DB_NAME(4) AS DatabaseName;
— Get DatabaseID from DatabaseName
SELECT DB_ID(‘tempdb’) AS DatabaseID;
Thank you so much! I fixed the error.
I appreciate it.
I love your articles and database scripts.
SIMPLE gets the job done :)
Thanks for the simple script.
is this really a post OR just a fomrality that you have to post something perday?
This is very useful script. Things like this we wonder all the time. No other blogger has to courage to write what we all want to read.
If you find it simple, you can read other stuff on blog.
We all are different level and our needs are different.
This website is very helpful to me,thans for sharing.
i declared local variable with VARCHAR(MAX).
But it stores only 8000 characters.what to do to store large amount of text into a varable.
DECLARE @VAR VARCHAR(MAX)
SET @X = ‘give more than 8000 chars’
Thanks for simple style you have used to explain sql stuffs.
It’ s cool and nice
Me too use to get confuse on db_name and db_id function , realy it’s use full
select * from sys.databases where database_id in(1,2,3,4)
does this query always returns below databases?
yes… Parasurama always it will return same output
Hello sir., I wanna know how to take the entire structure (tables,functions,stored procedures) of the database by using query like taking db script., can u help me..?
This is a great tip, but I notice this does not work across a linked server. It instead returns a NULL value for the DB_name. EX.
select db_name(dbid) As DB_Name,
str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N’ MB’ as Size
group by dbid
order by 2 desc
Hi. Just a followup to my previous question. Across a linked server, the results look like this:
NULL 452076.81 MB
NULL 90974.75 MB
tempdb 32799.88 MB
NULL 26025.88 MB
NULL 10729.69 MB
ReportServerTempDB 5990.94 MB
NULL 5900.94 MB
NULL 1395.06 MB
NULL 1214.88 MB
NULL 1210.00 MB
NULL 1122.94 MB
NULL 860.00 MB
NULL 359.07 MB
msdb 86.31 MB
NULL 61.06 MB
model 27.44 MB
ReportServer 17.00 MB
DataCollection 16.00 MB
master 6.50 MB
HI Pinal Dave,
Thanks for your helpful articles.
I have a question for you.
I have an SP on DB_1
I call DB_1..SP from DB_2
Is it possible to get the DB_Name(DB_2) in DB_1 when the SP is called (obviously without entering it by hand )?
awesome! It worked for me as a first step , can you also provide the code to get the file name in the database , if i have the file id.
DATABASE_ID of the DMV sys.dm_exec_requests is showing wrong is some of the Databse restore cases.
I am restoring a database Mars on a server
To check the database restore status i used sys.dm_exec_requests dmv and it showed me DATABASE _ID as 9 but the DATABASE_ID of Mars is 7.Pleach the DBIS names in the SYSDATABASES.
SELECT DATABASE_ID,* FROM sys.dm_exec_requests where COMMAND =’RESTORE DATABASE’
How to do that in MySQL/MariaDB??