How do I specify "close existing connections" in sql script

user_10884 0
Viewed 122304

I'm doing active development on my schema in SQL Server 2008 and frequently want to rerun my drop/create database script. When I run 

USE [master]
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')
DROP DATABASE [MyDatabase]
GO

I often get this error

Msg 3702, Level 16, State 4, Line 3
Cannot drop database "MyDatabase" because it is currently in use.

If you right click on the database in the object explorer pane and select the Delete task from the context menu, there is a checkbox which to "close existing connections"

Is there a way to specify this option in my script?

sql-server sql
8 Answers

if you are trying to drop it from you application, your connection string's initial catalog must be "master"

user_100265050

See below. Do not click OK, but Ctrl + Shift + N, and a new window with the script will be ready for you.

Drop Database Dialog for script generation

user_19778710

try this C# code to drop your database

public static void DropDatabases(string dataBase) {

        string sql =  "ALTER DATABASE "  + dataBase + "SET SINGLE_USER WITH ROLLBACK IMMEDIATE" ;

        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["DBRestore"].ConnectionString))
        {
            connection.Open();
            using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, connection))
            {
                command.CommandType = CommandType.Text;
                command.CommandTimeout = 7200;
                command.ExecuteNonQuery();
            }
            sql = "DROP DATABASE " + dataBase;
            using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, connection))
            {
                command.CommandType = CommandType.Text;
                command.CommandTimeout = 7200;
                command.ExecuteNonQuery();
            }
        }
    }
user_106258650
Related