SQL SERVER – A Quick Note on DB_ID() and DB_NAME() – Get Current Database ID – Get Current Database Name

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)

Previous Post
SQLAuthority News – Download SQL Server 2008 R2 Upgrade Technical Reference Guide
Next Post
SQL SERVER – Quick Look at SQL Server Configuration for Performance Indications

Related Posts

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.

    Reply
    • 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

      Reply
    • 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;

      Reply
  • Dear Sir,

    I love your articles and database scripts.

    SIMPLE gets the job done :)

    Thanks,
    Edwin

    Reply
  • Thanks for the simple script.

    Reply
  • is this really a post OR just a fomrality that you have to post something perday?

    Reply
    • 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.

      Reply
  • Nice One.

    Reply
  • khaled khelawy
    January 21, 2011 5:27 pm

    Nice !

    Reply
  • Srikanth Nallamothu
    January 22, 2011 3:36 pm

    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

    Reply
  • Dear dave,

    Thanks for simple style you have used to explain sql stuffs.
    It’ s cool and nice

    Reply
  • Me too use to get confuse on db_name and db_id function , realy it’s use full

    Reply
  • Parasurama Eswarappa
    May 24, 2012 5:30 pm

    select * from sys.databases where database_id in(1,2,3,4)

    does this query always returns below databases?
    master
    tempdb
    model
    msdb

    Reply
  • 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..?

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
    • 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.

      Reply
  • 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’

    Reply
  • Hi,

    How to do that in MySQL/MariaDB??

    Reply

Leave a Reply

Menu