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

master will always be there for any SQL Server instance - it's one of the four pre-defined, pre-created and must have databases on any SQL Server instance. I think doing a USE master is always safe. — marc_s
– marc_s, Commented Aug 25, 2021 at 5:07

Mitch Wheat · Accepted Answer · 2021-08-25 05:03:43Z

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]

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

USE master
GO

DROP DATABASE MyDB
GO

answered Aug 25, 2021 at 4:47

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1