聚沙成塔

ALTER PROCEDURE [dbo].[SP_SummaryDBSpace]
AS
BEGIN
------------------------------------------------
--step1: 查詢所有資料庫清單
IF OBJECT_ID('tempdb..#DBSpaceList') IS NOT NULL
	drop table #DBSpaceList;

select name
into #DBSpaceList  
FROM sys.databases with(nolock)
--where [name]='Services'
------------------------------------------------
--step2: 變數定義
Declare @DBName nvarchar(25)
Declare @sql nvarchar(MAX)
------------------------------------------------
--step3: 建立查詢結果彙總資料表
IF OBJECT_ID('tempdb..##DBSpaceListResult') IS NOT NULL
	drop table ##DBSpaceListResult;

create table ##DBSpaceListResult
(
	DatabaseName nvarchar(50),
	DatabaseSpace  decimal(5,2),
	Space_used  decimal(5,2), 
	FilePath nvarchar(100)
)
------------------------------------------------
--step4: by DB查詢空間可用量
Declare MyCursor Cursor FOR
Select [name] from #DBSpaceList   --for迴圈裡面的資料
Open MyCursor 
	Fetch NEXT FROM MyCursor INTO @DBName
	While (@@FETCH_STATUS <> -1)
	BEGIN
		--確保每一次使用都沒有資料表
		IF OBJECT_ID('tempdb..##temp_DBSpaceListResult') IS NOT NULL
			drop table ##temp_DBSpaceListResult
		--step1: 指定DB查詢相關物件
		set @sql='' ;
		set @sql= 'use '+ @DBName + ';';
		set @sql= @sql + 'DECLARE @tDBSize TABLE (DBName SYSNAME DEFAULT(DB_NAME()),Fileid INT, FileGroup INT, TotalExtents INT, UsedExtents INT, Name SYSNAME, FileName NVARCHAR(4000)) '
		set @sql= @sql + 'INSERT @tDBSize(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName) '
		set @sql= @sql + 'EXEC (''DBCC showfilestats'') '
		set @sql= @sql + 'SELECT DBName as DatabaseName,TotalExtents*64.0/1024/1024 as DatabaseSpace,UsedExtents*64.0/1024/1024 as Space_used, FileName as FilePath into ##temp_DBSpaceListResult FROM @tDBSize '

		BEGIN TRY
			EXECUTE sp_executesql @sql;
		END TRY
		BEGIN CATCH
			--print 'error' ; --跳出此次loop
		END CATCH 
		IF OBJECT_ID('tempdb..##temp_DBSpaceListResult') IS NOT NULL
			insert into ##DBSpaceListResult
			select 
				DatabaseName, 
				DatabaseSpace,
				Space_used,
				FilePath
			from ##temp_DBSpaceListResult
		Fetch NEXT FROM MyCursor INTO @DBName
	END

--關閉&釋放cursor
CLOSE MyCursor
DEALLOCATE MyCursor
 ------------------------------------------------
--確認OK後刪除
IF OBJECT_ID('tempdb..##DBSpaceListResult_step2') IS NOT NULL
drop table ##DBSpaceListResult_step2

select 
*
into  ##DBSpaceListResult_step2
from 
(
		select 
		DatabaseName, 
		'Used' as StatusType,
		DatabaseSpace,
		Space_used as DBSpace,
		FilePath
		from ##DBSpaceListResult
		union all
		select 
		DatabaseName, 
		'Unused' as StatusType,
		DatabaseSpace,
		DatabaseSpace- Space_used as DBSpace,
		FilePath
		from ##DBSpaceListResult
)as tmp


select * from ##DBSpaceListResult_step2

IF OBJECT_ID('tempdb..##temp_DBSpaceListResult') IS NOT NULL
drop table ##DBSpaceListResult

END
GO

 

同時也歡迎追蹤Tableau Public Gallery- MR.360 |聚沙成塔，裡面包含文章中的案例實作，
期待能帶給您新的啟發或靈感。

未來文章將喬遷新址「一趟數據分析之旅」，歡迎追蹤繼續支持，您將不會錯過任何新知識。