How to Find Database Name in SQL Server Using Query?

by

In this SQL Server tutorial, you will learn how to find database name in SQL Server using query.

You will see about two methods that you can use to find the name of the database. Then, you will understand the situation where you can employ those methods.

Find Database Name in SQL Server Using Query

To find database names in SQL Server using query, you have two ways: first, you can find the name of the database using the database ID, or you can use a function that shows the name of the currently connected database.

SQL Server has a system catalog view from which you can find the information related to databases. To retrieve the name of the database, use the below syntax:

SELECT name FROM sys.databases
WHERE database_id = databaseID;

Where,

  • sys.databases: It is the system catalog view that stores data about all the databases in SQL Server.
  • databaseID: The id of the database whose name you want to retrieve.

Now to use the above syntax for finding the name of the database, follow the below steps:

Open the command prompt on your computer and connect to the SQL Server instance using the below command.

sqlcmd -S MSI\SQLEXPRESS

Suppose you remember the database ID which is 5 in this case, and you want to retrieve the name of that database using the ID. For that use the below query.

SELECT name FROM sys.databases
WHERE database_id = 5;
GO
Find Database Name in SQL Server Using Query

When you execute the above query, you get the Orders database whose ID is 5, So whenever you have to find the name of the database, and you only know the ID, you can use the above query to find the name of any database.

Next, you will find the name of the database to which you are currently connected, and this one is the second way to find the database name in SQL Server using a query. For that, you use the below syntax.

SELECT DB_NAME()

Where DB_NAME() is a function that returns the name of the current database on which you are working.

Again connect to the SQL Server instance using SQLCMD as you have done above, after connecting, you don’t know about the current database to which you are connected.

To find the current database name run the below query.

SELECT DB_NAME();
GO
DB_NAME() Function Find Database Name in SQL Server Using Query

After execution of the above query, it shows the name of the current database, which is master in this case. If you are connected to a different database, you will see the name of that database.

This is how to find database names in SQL Server using query. Next, you need to know what are the situations where the above two ways to find the name of the database can be extremely helpful.

  • No one can escape from the error in the database, and error is certain to happen; suppose you are a developer and encounter an error related to the performance. In that case, it is a must to find the database responsible for that kind of error.
    • So, using the above method, you can find the name of the database and take action accordingly.
  • Larger companies use multiple databases under an SQL Server instance, and sometimes it can be very challenging to keep track of all the databases when you haven’t set up the server yourself.
    • In that case, if you know how to find the name of the database quickly using the above two queries, then you can save time.
  • Whenever any failure occurs to the database or data loss to your database, at that time you need to recover the database to its previous state. Remembering the name of the database is very important in this case.
    • Using the above first method or way you can verify the name of the database that you want to restore while reducing the risk of accidentally overwritting the wrong database.

These are some situations where you use the above two methods to find database names in SQL Server using a query. However, there can be other situations that require finding the name of the database.

Conclusion

In this SQL Server tutorial, you learned how to find the name of the database using a query in SQL Server, then learned about two different queries to find the name of the database, one with database ID and the other with function. In the end, you learned about some situations where you can use that query to find the name of the database.

You may also like: