Dropping and recreating databases in Microsoft SQL Server

Question

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?

Answer 1

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

Answer 2

+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