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

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