Dropping and recreating databases in Microsoft SQL Server

If you are unable to drop and create database in SQL server, 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

/*********************************************************************************

                                                        OR

*********************************************************************************/

DECLARE @DBName varchar(50) = ‘yourDBname’

USE master

IF EXISTS(select * from sys.databases where name= @DBName)

EXEC(‘DROP DATABASE ‘ + @DBName)

EXEC(‘CREATE DATABASE ‘ + @DBName)

RELATED POSTS

Install Adventure Works database from backup

The Adventure Works 2014 Data Warehouse database can be installed by restoring a database backup.Read More