The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

如何替 SQL Server 2012 LocalDB 卸載所有使用者資料庫

每次用 Visual Studio 2012 開發一個新的、有用到 LocalDB 資料庫的專案,只要專案執行起來,就會將資料庫自動掛載到 SQL Server 2012 Express LocalDB 的執行個體裡,所以用一段時間後自然也會有一堆資料庫在上面,但大部分資料庫應該都是可以卸載 (Detach) 的,因此我自己寫了一支 T-SQL 可以批次卸載所有使用者資料庫,以便看到一個乾淨的 LocalDB 執行個體。

以下就是我這次寫的 T-SQL 內容,直接剪貼到 Management Studio 執行即可:

DECLARE @DatabaseName NVARCHAR(200)

DECLARE dbs CURSOR FOR
	SELECT name
	FROM master..sysdatabases
	-- dbid 若為 1 ~ 4 都是系統資料庫
	WHERE dbid > 4

OPEN dbs 

FETCH NEXT FROM dbs INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN

	PRINT N'正在卸載 [' + @DatabaseName + N'] 資料庫'
	
	USE [master]

	BEGIN TRY
		PRINT N'    將 [' + @DatabaseName + N'] 資料庫設定為「單人模式」'
		EXEC(N'ALTER DATABASE [' + @DatabaseName + N'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
	END TRY
	BEGIN CATCH
		PRINT N'    將 [' + @DatabaseName + N'] 資料庫設定為「離線」'
		EXEC(N'ALTER DATABASE [' + @DatabaseName + N'] SET OFFLINE')
	END CATCH

	PRINT N'    將 [' + @DatabaseName + N'] 資料庫卸載'
	EXEC master.dbo.sp_detach_db @dbname = @DatabaseName, @skipchecks = true

    FETCH NEXT FROM dbs INTO @DatabaseName
END

CLOSE dbs
DEALLOCATE dbs

 

因為 LocalDB 的執行個體只要沒人連線,就會自動關閉,而且一般來說開發環境異動性也比較大,資料庫的 mdf 檔案可能也會搬動,因此當資料庫 mdf 資料檔移走之後,用 Management Studio 連接 LocalDB 執行個體依然會看到這些資料庫名稱,而且這些資料庫將無法被卸離,必須先將資料庫設定為「離線」,然後才能「卸離」,這個卸離資料庫的小技巧我也加進了這個 T-SQL: 指令檔裡,各位可以注意看一下。

 

除此之外,如果你只想要卸離那些「已經沒有 SQL 資料檔在的資料庫」,這個 T-SQL 微調一下就能達成這個目的,請參考以下 T-SQL 語法:

DECLARE @DatabaseName NVARCHAR(200)

DECLARE dbs CURSOR FOR
	SELECT name
	FROM master..sysdatabases
	-- dbid 若為 1 ~ 4 都是系統資料庫
	WHERE dbid > 4
OPEN dbs 

FETCH NEXT FROM dbs INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN

	PRINT N'正在卸載 [' + @DatabaseName + N'] 資料庫'
	
	USE [master]

	BEGIN TRY
		PRINT N'    將 [' + @DatabaseName + N'] 資料庫設定為「單人模式」'
		EXEC(N'ALTER DATABASE [' + @DatabaseName + N'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
	END TRY
	BEGIN CATCH
		PRINT N'    將 [' + @DatabaseName + N'] 資料庫設定為「離線」'
		EXEC(N'ALTER DATABASE [' + @DatabaseName + N'] SET OFFLINE')
		
		PRINT N'    將 [' + @DatabaseName + N'] 資料庫卸載'
		EXEC master.dbo.sp_detach_db @dbname = @DatabaseName, @skipchecks = true
	END CATCH

    FETCH NEXT FROM dbs INTO @DatabaseName
END

CLOSE dbs
DEALLOCATE dbs