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) DatabaseID;
-- Get DatabaseID from DatabaseName
SELECT DB_ID('tempdb') DatabaseName;
-- Get all DatabaseName and DBID
SELECT name,database_id
FROM sys.databases;

Reference: Pinal Dave (http://blog.SQLAuthority.com)

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

    • 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

      Like

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

      Like

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

    Like

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

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

    Like

  3. 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..?

    Like

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

    Like

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

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

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

    Like

  8. 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’

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s