How to get default database name using Transact-SQL query?

Question

My initial concern was to DROP DATABASE MyDB when a certain condition is met but I get this error:

Database 'MyDB' is still in use

So, the answer I found was to do

USE master 
DROP DATABASE MyDB

It works, but for my case there is no guarantee whether master will be the default database on the server or not.

I would like to know how do I get the default database name for that particular server so that I can write something like

USE Default_DB 
DROP DATABASE MyDB

Answer 1

The default database is set per login, not for the server. If not specified when creating a login, it defaults to 'master'.

You change it like this:

ALTER LOGIN [user_name] WITH DEFAULT_DATABASE = [default_database]

Ref: CREATE LOGIN

To answer your actual question, you should always move to 'master' to delete a database:

USE master
GO

DROP DATABASE MyDB
GO