Is there a command to stop using a database in SQL Server?

Question

I'm refering to when we say USE dbTest we start using that database and we can create tables and what not, and if we want to change databases we could just say USE dbNotatest and it would change the database we're using.

But is there a way to stop using the database we selected in the first place, without starting to use another one?

Answer 1

To stop using a database, you will need to change your database context. For example, if you are trying to drop your database and you are in the context of that database, simply switch to another database (commonly master or tempdb).

If other connections are open to the database and preventing you from dropping the database, you will need to kill the connected spids. This can be tedious, so an option to force close all connections and then drop your database that usually works for me is:

use [master];
ALTER DATABASE [foo] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [foo] SET ONLINE;
DROP DATABASE [foo];

By taking the database offline with rollback immediate, I force all connections closed and rollback any open transactions. Now, I could drop it while it is offline, but if I do the database files will remain on the file system. Dropping a database online will remove the database files, so I bring it back online before I drop it.