Sometimes you need to drop a database from within code and without using SQLDMO. But DROP DATABASE will fail if there are any active connections exist. So below is a small script that kills all active connections on particular database and drops the database at the end. Remeber to replace <<DATABASE_NAME>> with your actual database name :)
1: SET
2: NOCOUNT ON
3: DECLARE
4: @sql varchar(4000),
5: @DatabaseName varchar(50)
6:
7: SET
8: @sql = ''
9: SET
10: @DatabaseName = '<<DATABASE_NAME>>'
11:
12: IF db_id(@DatabaseName) < 4 RETURN
13:
14: SELECT @sql=COALESCE(@sql,',' )+'kill '+CONVERT(VARCHAR, spid)+ '; '
15: FROM master..sysprocesses WHERE dbid=db_id(@DatabaseName)
16: IF LEN(@sql) > 0 EXECUTE(@sql)
17:
18: SET
19: @sql = 'DROP DATABASE ' + @DatabaseName
20: EXECUTE
21: (@sql)
Technorati Tags:
TSQL,
Database
posted @ Monday, July 23, 2007 2:02 PM