After I read Greg Low’s post and my initial try, I kept on thinking that I need to come up with a solution that in theory is robust. The only way I can think of is to use a CLR stored procedure which will do the following three steps:
1. alter database <blah> set offline with rollback immediate;
2. find all the files that compose the <blah> database, using
select physical_name from master.sys.master_files where database_id = db_id(‘blah’)
3. delete each file found in step 2.
So here is C# code to create the CLR stored procedure:
1: using System;
2: using System.Collections;
3: using System.Data;
4: using System.Data.SqlClient;
5: using System.Data.SqlTypes;
6: using Microsoft.SqlServer.Server;
7: using System.IO;
8: 9: public partial class StoredProcedures
10: { 11: [Microsoft.SqlServer.Server.SqlProcedure]12: public static int usp_DropDB (SqlString db_name)
13: { 14: 15: try
16: {17: using (SqlConnection conn = new SqlConnection("context connection=true"))
18: {19: string sql = "select cnt=count(*) from master.sys.databases where name=@dbname";
20: SqlCommand scmd = new SqlCommand();
21: SqlParameter parmDBName = new SqlParameter("@dbname", SqlDbType.VarChar, 128);
22: parmDBName.Value = db_name.Value; 23: scmd.Parameters.Add(parmDBName); 24: scmd.CommandText = sql; 25: scmd.Connection = conn; 26: conn.Open(); 27: Int32 i = (Int32)scmd.ExecuteScalar(); 28: conn.Close(); 29: scmd.Dispose(); 30: 31: if (i == 1)
32: { 33: 34: sql = "if exists (select * from sys.databases where name=@dbname)";
35: sql = sql + "\r\n alter database [" + db_name.Value + "] set offline with rollback immediate;";
36: 37: SqlCommand cmd = new SqlCommand();
38: SqlParameter pDBName = new SqlParameter("@dbname", SqlDbType.VarChar, 128);
39: pDBName.Value = db_name.Value; 40: 41: 42: cmd.Parameters.Add(pDBName); 43: 44: cmd.CommandText = sql; 45: cmd.Connection = conn; 46: 47: conn.Open(); 48: cmd.ExecuteNonQuery();49: cmd.CommandText = "select physical_name from master.sys.master_files where database_id=db_id(@dbname)";
50: SqlDataReader rd = cmd.ExecuteReader();51: ArrayList alFilePath = new ArrayList();
52: while (rd.Read())
53: {54: alFilePath.Add((string)rd.GetString(0));
55: } 56: conn.Close(); 57: 58: cmd.CommandText = "drop database [" + db_name.Value + "];";
59: 60: conn.Open(); 61: cmd.ExecuteNonQuery(); 62: conn.Close(); 63: 64: for (i = 0; i <= alFilePath.Count - 1; i++)
65: {66: File.Delete((string)alFilePath);
67: } 68: }69: else
70: {71: return 1; // no db found
72: } 73: }74: return 0; // succeed
75: }76: catch
77: {78: return (-1); // -1 meaning error occurred
79: } 80: } 81: }
Using Visual Studio to compile this to DLL file, let’s assume the file is called/located at c:\temp\CLR_SP.dll, and then using the following sql statement to create the CLR procedure:
1: use master
2: create assembly clr_dropDB from 'C:\temp\CLR_SP.dll'
3: with permission_set = External_ACCESS;
4: go
5: 6: create proc dbo.usp_DropDB @db_name nvarchar(128)
7: as external name clr_dropDB.StoredProcedures.usp_DropDB;
8: go
9: 10: -- example
11: use master;
12: declare @id int;
13: exec dbo.usp_dropDB @db_name=N'testdb';
14: print @id;
My dev/test environment is Visual Studio 2012 Professional and SQL Server 2012 Developer Edition.
