Dropping and recreating databases in Microsoft SQL Server

I am experimenting and learning with Microsoft SQL Server 2008 R2 SP1. I have a database where I made many experiments. Now I would like to drop and recreate it. So I extract the creation script from database, I delete it and I use the script to recreate it. To my surprise, all the tables, keys etc are still there. How do I drop the database, so that I can rebuild the database from scratch?

USE master
IF EXISTS(select * from sys.databases where name='yourDBname')
DROP DATABASE yourDBname

CREATE DATABASE yourDBname

+1 to AnandPhadke for his part of the code

This code will close all active connections to the database and then drop it

WHILE EXISTS(select NULL from sys.databases where name='YourDBName')
BEGIN
    DECLARE @SQL varchar(max)
    SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
    FROM MASTER..SysProcesses
    WHERE DBId = DB_ID(N'YourDBName') AND SPId <> @@SPId
    EXEC(@SQL)
    DROP DATABASE [YourDBName]
END
GO

CREATE DATABASE YourDBName
GO

Requiring the DBName to be typed more than once is error prone, at some point it'll be executed with inconsistent entries and unintended consequences.

The answers from AnandPhadke or Pierre with variable support would be preferred for me.

DECLARE @DBName varchar(50) = 'YourDatabaseName'
USE master
IF EXISTS(select * from sys.databases where name= @DBName)
EXEC('DROP DATABASE ' + @DBName)

EXEC('CREATE DATABASE ' + @DBName)

or

DECLARE @DBName varchar(50) = 'YourDatabaseName'
WHILE EXISTS(select NULL from sys.databases where name = @DBName )
BEGIN
    DECLARE @SQL varchar(max)
    SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DBName) AND SPId <> @@SPId
    EXEC(@SQL)
    EXEC('DROP DATABASE ' + @DBName)
END
GO

SQL Server 2016 (and above) support one line and atomic(?) syntax DROP DATABASE IF EXISTS database_name

REF: https://msdn.microsoft.com/en-us/library/ms178613.aspx

> I extract the creation script from database

This extract the creation script for everything in the database (tables, keys etc). If you simply want to create an empty database, just run CREATE DATABASE <dbname>

This works best for me:

if exists (select name from sys.databases where name='YourDBName')
alter database YourDBName set single_user with rollback immediate
go
if exists (select name from sys.databases where name='YourDBName')
drop database YourDBName