by

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

100 thoughts on “SQL SERVER – Get Current Database Name”

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

  18. 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”?

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

      Reply

      • 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

        Reply

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

          Reply

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

    Reply

  32. 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’)

    Reply

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

    Reply

  38. @navin

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

    ~IM

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

    • @Eman

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

      This is called as four-part-name.

      ~ IM.

      Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

Leave a Reply

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

Gravatar
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 )

Cancel

Connecting to %s