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)
22 Comments. Leave new
Hi Pinal,
I am an ardent fan of you for your commitment to SQL Server knowledge sharing forums.
Its a very interesting tip.
Regards,
DP.
Hi All,
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;
Dear Sir,
I love your articles and database scripts.
SIMPLE gets the job done :)
Thanks,
Edwin
Thanks for the simple script.
is this really a post OR just a fomrality that you have to post something perday?
Ashish,
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.
Roger.
Nice One.
Nice !
hello pinal
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.
ex:
DECLARE @VAR VARCHAR(MAX)
SET @X = ‘give more than 8000 chars’
PRINT @X
Dear dave,
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?
master
tempdb
model
msdb
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
from [LinkedServerName].[DBName].[sys].[sysaltfiles]
group by dbid
order by 2 desc
GO
Hi. Just a followup to my previous question. Across a linked server, the results look like this:
DB_Name Size
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 )?
Many thanks
Nick
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.
Example
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’
Hi,
How to do that in MySQL/MariaDB??