Dropping and recreating databases in Microsoft SQL Server

Solution 1:

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

CREATE DATABASE yourDBname

Solution 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

Solution 3:

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

Solution 4:

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

Solution 5:

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>