1.KillConnectionsAndDropTheDb.sql

-- START SCRIPT 1.KillConnectionsAndDropTheDb.sql 

use master ; 

declare @DbName varchar(100) 

-- Set the name of the database to restore

set @DbName = '$(DbName)'

/****** Object:  Database [CAS_DEV]    Script Date: 03/14/2010 11:35:15 ******/

IF  EXISTS (SELECT name FROM sys.databases WHERE name = @DbName)

begin --if there exists a 

declare @backupFileName varchar(100), @restoreDirectory varchar(100),

@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),

@databaseDataFile varchar(100), @databaseLogFile varchar(100)

, @execSql nvarchar(1000)

set @execSql = ''

-- Create the backup file name based on the restore directory, the database name and today's date

--@backupFileName = @restoreDirectory + @DbName + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'

--declare @DBFileName varchar(256)    

--set @DBFileName = @DbName + '_' + replace(convert(varchar, getdate(), 112), '-', '.') + '.bak'

-- Get the data file and its path

select @databaseDataFile = rtrim([Name]),

@databaseDataFilename = rtrim([Filename])

from master.dbo.sysaltfiles as files

inner join

master.dbo.sysfilegroups as groups

on

files.groupID = groups.groupID

where DBID = (

select dbid

from master.dbo.sysdatabases

where [Name] = @DbName

)

-- Get the log file and its path

select @databaseLogFile = rtrim([Name]),

@databaseLogFilename = rtrim([Filename])

from master.dbo.sysaltfiles as files

where DBID = (

select dbid

from master.dbo.sysdatabases

where [Name] = @DbName

)

and

groupID = 0

--KILL ACTIVE CONNECTIONS START 

SELECT 'Killing active connections to the "' + @DbName + '" database'

-- Create the sql to kill the active database connections

set @execSql = ''

select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' ; '

from master.dbo.sysprocesses

where db_name(dbid) = @DbName

and

DBID <> 0

and

spid <> @@spid

exec (@execSql)

SELECT @execSql AS 'execsql'

--select @execSql

--SELECT * FROM master.dbo.sysprocesses where db_name(dbid)='CAS_DEV'

--==================================================================

-- kill ACTIVE CONNECTIONS END 

--===================================================================

--==================================================================

-- START -- DROP DATABASE 

--===================================================================

SELECT 'Dropping the ' + @DbName + ' database'

select @execSql = '

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N''' + @DbName + ''')

DROP DATABASE ' + @DbName + ' ;'

SELECT 'RUNNING THE DROP DB QUERY :'

SELECT @execSql

exec sp_executesql @execSql

--==================================================================

-- END -- DROP DATABASE 

--===================================================================

end --eof if

--SELECT SPID FROM master.dbo.sysprocesses

--where db_name(dbid) = 'CAS_DEV'

--'kill ' + convert(char(10), spid) + ' '

--from master.dbo.sysprocesses

--where db_name(dbid) = @DbName

--and

--DBID <> 0

--and

--spid <> @@spid

-- END SCRIPT 1.KillConnectionsAndDropTheDb.sql