as title
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 |聚沙成塔,裡面包含文章中的案例實作,
期待能帶給您新的啟發或靈感。
未來文章將喬遷新址「一趟數據分析之旅」,歡迎追蹤繼續支持,您將不會錯過任何新知識。