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)

SQL Scripts
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.

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


  • Nice One.

  • khaled khelawy
    January 21, 2011 5:27 pm

    Nice !

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

    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

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

  • 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

  • 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


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

  • Hi,

    How to do that in MySQL/MariaDB??


Leave a Reply