Knowing your Database
 
One common requirement for people who write SQL Server scripts is to know the name of the current database in script without having to hardcode it inside the code. This will ensure that your scripts are reusable and will work across all databases. How do you determine the name of your current database?? Well, there are two methods that you can use:
  • The first method is to use the information contained in the sysdatabases system table. This table is present only in the master database. This table contains information for each database in SQL Server.
  • The second method is to use the built-in function called db_name.

In this article, we will see both these methods.

Method A

In the first method, we use the information contained in the sysdatabases system table. The information in this table is identified using the database ID which is a smallint. Thus, to fetch the name of the database that you are currently working on, you will need to know the database ID of your database and then use it fetch information. Here is how you can do it.


SELECT name FROM master..sysdatabases WHERE dbid = DB_ID()
 
The db_id function is a built-in function that returns the identifier of the current database when used without any parameters. You can also supply the name of a datbase to get its identifier. You can wrap the call to the above statements inside of a stored procedure or a function so that it is reusable.
 
Method B
 
Well, SQL Server folks have already thought about our reusable idea in Method A :-) You have a function called db_name() that does the same job. This function when used without any parameters gives the name of the current database. You can also pass in a database identifier and then get the name of that database. Here is how you can use this function:

SELECT name = DB_NAME()
 
OK, so which method will you use?? You should use the second method unless you need more information about the database apart from its name, in which case you can write a function or a procedure that returns all the information that you want.

Home