SQL SERVER – Get Current Database Name

Yesterday while I was writing script for SQL SERVER – 2005 – Find Unused Indexes of Current Database . I realized that I needed SELECT statement where I get the name of the current Database. It was very simple script.

SELECT DB_NAME() AS DataBaseName

It will give you the name the database you are running using while running the query.

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

About these ads

106 thoughts on “SQL SERVER – Get Current Database Name

  1. I needed to run a report that spanned multiple servers, as well as most of their databases. This works very well combined with the undocumented sp_MSforeachDB.

    sp_MSforeachDB ‘use [?]; select user_code, u_name, superuser, lastlogin, groups, db_name() as database_name from ?..OUSR;’

    Using the above allows me to search every database on the server for information in the OUSR table. If the table is not found it will of course throw an error message. But this works great for running a quick report.

  2. @Santhost v Kumar

    Run this command in the database whose file location you want to know.

    select filename from sysfiles

    to get more details

    select * from sysfiles

    It will display all the files (.mdf and .ldf ), their physical names ( existing on Operating System) and logical names ( used inside sql server ) and location of these files on Operating system.

    You can also check this using SSMS.

    > Open SQL Server Management Studio.
    >Open Object Explorer ( click View located on top ,select Object Explorer )
    > Expand Databases
    > Right click database name , click properties
    > A new Dialogue box will open, Select Files from left side List names. on the right side, slide bar to the right to see more information, you can see Location of database files on Operating System ( this is under Path column ).

    Regards
    IM.

  3. Hi,

    If that database is on the same server, then you can use

    DBNAME.dbo.TableName to access the value from the table

    Thanks,

    Tejas

  4. Hi….
    I wan to get All the DataBase Names in server
    Can you give me a Query………
    And ther above query is very usefull……..
    Thanking u
    Regards
    Pavan

  5. how can i get db name from where the record retrieved
    for ur info: i use union syntax
    for example:
    DBName CustNo CustName
    db1 cust1 custA
    db1 cust2 custB
    db2 cust1 custA
    db3 cust1 custA

  6. I am running a stored procedure from the master database in a database named “FOO”. Executing DB_NAME() returns “master”. How do you return “FOO”?

  7. I want to supply db name from ouitside.how its posible

    like
    declare @DBNAME sysname ,@stm varchar(100)
    Set @DBNAME =’database name ‘
    use database name

  8. Hi…
    can i get Current Selected Database name in Variable and that variable can be used with USE [] command?

    like

    DECLARE @DBName sysname
    SET @DBName = DB_NAME()
    USE [@DBName]

    It gives below error:
    Could not locate entry in sysdatabases for database ‘@DBName’. No entry found with that name. Make sure that the name is entered correctly.

  9. Padma,
    You can get list of the tables from a database with folowing query

    select name from sysobjects where type=’U’

    here
    sysobjects is a system table containg all information of the objects ina database like tables,vies,SP etc..

    type-> User Defined Table..

    if you use type=’P’ you will get all procedures …

    also try this .. select * from sysobjetcs

  10. Milan you can use current databse name in a script..

    try like this..

    declare @DBName varchar(50)
    set @DBNAme=(select db_name())
    use @DBName
    go

    • Hi Vishnu,

      I have the same problem, I wanted to use current database name in a variable and then use that variable in USE command. I tried your commands but it didn’t work, I got an error:

      Msg 102, Level 15, State 1, Line 3
      Incorrect syntax near ‘@DBName’.

      What would be the reason, or is there any other way to do that.

      I want to run a script in all the databases in a server.

      Thanks.

      • Hello Shoaib,

        You can use sp_msforeachdb stored procedure. Everywhere in script “?” (question mark) will be replaced with database name and the script will be executed for each database. For example to perform sp_helpdb for each database:
        sp_msforeachdb ‘sp_helpdb ?’

        Regards,
        Pinal Dave

        • Hi Pinal,

          Thanks for the response.

          However, I am still not able to run the script against all the database.

          Because, I have a big script, not just a stored procedure like SP_HELPDB you used.

          e.g. if I run: select name from sys.sysusers statement in every database INDIVIDUALLY, it would give different numbers, but when i run

          sp_msforeachdb ‘select name from sys.sysusers ?’

          it gives the numbers from current database * total number of databases.

          Like if I use Master, the result is 30 and total databases on the server are 38 so total rows 1140. also same result for every database.

          I hope you understand my question.

          I want to use USE command (or any other way to change the database name in db_name) then run my big script.

          Thanks for your time.

  11. Joseph !!
    You can get all DB names in a current server by using following query…. it is pretty usefull while writing some SQL scripts or creating Procedures…

    select name from master..sysdatabases

  12. Great … thanks I´ve been looking for this. I needed a script that would change settings for the current database… did it this way:

    Declare @DBname nvarchar(50)

    SELECT @DBname = DB_NAME()

    exec (‘ALTER DATABASE ‘ + @DBname + ‘ SET TRUSTWORTHY ON’)

    exec (‘ALTER AUTHORIZATION on DATABASE::’ + @DBname + ‘ to sa’)

  13. how to get all database name from a sql server without run sql script, but use some command or utility like
    “osql -L > .\server.list”

  14. @navin

    Select name from sys.databases — SQL Server 2005 and above
    select name from sysdatabases — SQL Server 2000

    ~IM

  15. How do we get this work?

    The first set of query to get MNO_V12 database will be replaced by a function in ABC_V15 database.

    Reason we need this query is that MNO_V12 is not controlled by us and we need a single place to make the switch.
    —-
    Use Master
    Go
    declare @db_name as sysname
    select @db_name= name from sysdatabases
    where name = ‘MNO_V12′
    Use ABC_V15
    Go
    SELECT c.CustomerName
    FROM [@db_name].dbo.tbl_customers c
    where c.customerid = 70
    —-

    Thanks

    Kartik

  16. My problem is I am trying to make code generic. I have 3 sites with their own servers. Site one has a Server referred to as Barcode as well as Site 2 and 3. These are all different server names and all have the same database name on them like Infocenter. When I write a stored procedure what I want to do is call a function against a UDT table with Servers Config. So on Site 1 Barcode is NC-XXX-DB at Site 2 Barcode is SA-XXX-DB. So instead of remember which stored procedures I need to change before I take them to site 2 I should be able to call the function. I can get the function to work but concatenating the server name to the database is not working. here is just a test string I was using. Does anyone have a suggestion at what I am doing wrong? Or would Dynamic SQL be better.
    Select * from (Select Localization.dbo.GetServerName(‘BARCODE’)) Navision_intergration.dbo.Item_Update

  17. Here is my scenario i have sets of 2 databases and a view from 1 database refers the other so whenever i make a change i have to go to all the sets and manually adjust it instead of just running a single script in all the sets. My question here is can i make the view dynamic so it does not matter which database i am refering to i tried the following but it did not work

    Select * from Substring((select db_name),4,7) + ‘Domain’.dbo.Account

    Thanx

  18. hello ,
    can u help me plz….

    i want to select from a table in a database in server1 and then insert the result in other table in different database

    select name from table (server name1)
    insert into table values() (server name2)

    • @Eman

      Insert into Servername1.databasename1.ownername1.tablename
      select * from Servername2.databasename2.ownername2.tablename2

      This is called as four-part-name.

      ~ IM.

  19. rafique, the below is what I use. I wrote some if it, got bits and pieces from all over.

    USE master
    DECLARE d CURSOR FOR SELECT name FROM sysdatabases
    WHERE name NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’) ORDER BY crdate, name
    DECLARE @db varchar(255), @next_db varchar(255), @bak varchar(255), @USESQL NVARCHAR(max)
    OPEN d
    FETCH NEXT FROM d INTO @db
    WHILE (@@FETCH_STATUS-1)
    BEGIN

    —-Enter here the stuff you want to run on each database

    FETCH NEXT FROM d INTO @next_db
    IF (@@FETCH_STATUS=-1) BREAK
    SELECT @db=@next_db
    END
    DEALLOCATE d

  20. hello ,
    can u help me plz….

    i want to select from a table in a database in server1 and then insert the result in other table in different database

    select name from table (server name1)
    insert into table values() (server name2)

  21. wow fantastic articl

    can u help me plz

    want to kno everything about sql without doing any studiezz

    please summarize SQL books on line

    than Q

    regards

  22. Thank you
    Can I make a tag or something else to mark my database
    if I want to use it in search for a specified database .
    Something like :
    SELECT [database name]
    FROM “any where”
    WHERE
    “tag = [my value]”

    Best regard.

  23. Pinal,
    i need your help,
    i want to take a value of one field from other database,
    but the database name is variable (it defined in current database), and the last field result want to store to memvar @Result, suppose i make script like this:

    DECLARE @Sql VARCHAR(200),
    @DatabaseName_CRM VARCHAR(30),
    @Result VARCHAR(500)

    SET @DatabaseName_CRM = ‘CRM_MSCRM’
    SET @Sql = ‘SELECT TOP 1 @Result = Address1_Line1 FROM ‘ + @DatabaseName_CRM +
    ‘.dbo.Account WHERE Address1_Line1 IS NOT NULL’
    EXEC (@Sql)

    — error :>
    — Must declare the scalar variable “@Result”.

    do you have any idea ?

    Big Thanks
    Boim

  24. In adition to all this i recomend what also Pinal Dave suggested. To know everything about a database or all of them use

    sp_helpdb

  25. I am trying to design a UI so internal users can backup their DB easily and the ability to find out the DB name is very useful. Thanks!

  26. how to list all database name and table names(describe table) in a server.
    ie,
    ————————
    db_name: DB1
    table_name:table1
    attribute_name data type width
    a varchar 50
    b varchar 50
    …………………………………………….
    table_name:table2
    attribute_name data type width
    c varchar 50
    d varchar 50

    ———————————
    db_name: DB 2
    table_name:table 4
    attribute_name data type width
    a varchar 50
    b varchar 50

  27. hi,

    it’s good one.

    but, i want to know “how many databases in that particular server”?
    how to know?
    please post it to [email removed]

  28. Great post. While this script was really simple, but it saved me from hours of trial and error operations to get the name of the current database.

    I was planning to use sys.databases catalog view for that.

    Thanks.

  29. please help me…

    In my form -button1 -name: conn
    button2: name: orders
    Insert data into the table with the help of dataset, datagridview and command builder class
    * command builder class is used to create DML syntaxes
    * command builder class works with the help of data adapter class

    i am write code in below way but cannot execute this code and after build my program error msg is displayed:

    error msg like this: Error 1 Non-invocable member ‘System.Data.DataSet.Tables’ cannot be used like a method. D:\sudhakar\webapp\database\database\Form1.cs 51 43 database

    private void conn_Click(object sender, EventArgs e)
    {
    //SqlConnection con = new SqlConnection();
    con = new SqlConnection(“server=COMPUTER-9C3215; uid=sa; pwd=btp@123; databse=sudha”);
    con.Open();
    MessageBox.Show(“connection is opend”);
    // string q=””;
    SqlDataAdapter da = new SqlDataAdapter(“select * from test”, con);
    da.Fill(ds, “test”);
    //DataSet1 ds = new DataSet1();
    DataSet ds = new DataSet();
    MessageBox.Show(“data is filled:” + con.State.ToString());
    }

    private void button4_Click(object sender, EventArgs e)
    {

    dataGridView1.DataSource = ds.Tables(“t”);
    }

    Note: My error is :Error 1 Non-invocable member ‘System.Data.DataSet.Tables’ cannot be used like a method. D:\sudhakar\webapp\database\database\Form1.cs 51 43 database

    after build: keyword is not supported

  30. Hi, I have my database on a shared database server. I have been not using that database for quite some time now and now I need to retreive some old data. All I remember are few table names in the database. Is there a way, to find my database name on a shared database server using the table names?

  31. 1) I have 2 db one is parent db and another one is child db.

    2) suppose while using child db i need parent table information normall we are using parent..tablename.

    3) statically we declare @db=parent at that time we use exec() the query.

    for ex:

    declare @DB varchar(20)
    set @db= parent

    exec
    (
    ‘select * from child inner join
    (
    SELECT distinct ia_grn_num FROM ‘+@DB+’..invtb_adjust where ia_stock_num = ”TYREI”
    ) b on ir_grn_num = b.ia_grn_num’
    )

    4) I want to use set @db= parent but i dont want to use exec() the query. Is it posible to sql server?

  32. Hi Dave, similar question: I have multiple DBs with similar Schemas (for different levels of development). I need a way to dynamically specify which of these schemas is the one I want.

    Kind of like a: “USE [Ask me for which DB and insert it here]” statement at the top of the script.

    Any ideas?

    -Ralph

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

  34. MSSQL – How do you find which db the query is running.
    i am aware of db_name()

    query is :

    select dbname1 from dbname1.table1
    union all
    select dbname2 from dbname2.table2

    here i am using union to query two dbs.
    i do i get dbname1, dbname2 in output column automatically.

    in this case i cannot use db_name()

  35. Hello. I’m hoping for a solution to an issue I have.

    There seems to be an explicit name set in a stored procedure, to a database that I had renamed for the sake of doing lab work.

    The original name of the database was FOO1, and I restored it as FOO2. I’m using a tool in learning how to reverse engineer a database when I noticed that I have quite a few errors because of a reference in a Stored Procedure that references FOO1 explicitly.

    The error I’m getting is:
    Unable to resolve object ‘FOO1′.[myTable] in unknown ‘[MyServer].[FOO1]’ referenced by stored procedure ‘[MyServer].[FOO2].dbo.[myStoredProcedure]’

    So I understand the error it’s looking for something in what was the original name of the database.

    I found the line in the stored procedure that makes this reference:

    FROM FOO1.dbo.Usertable p, ….

    Is there a way (in a stored procedure) to automagically get database name, set it as a variable for example as, @DBNAME, so that I can change it to look like:

    FROM @DBNAME.dbo.Usertable p …

    Could someone please provide insight :)

    Greatly appreciated.

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