检查MSSQL服务器中所有数据库的MDF文件的总大小以及已用大小

 

今天一网友咨询如何获取MSSQL服务器中所有数据库的文件的总大小以及已用大小
虽然 EXEC ('dbcc showfilestats') 能够获取当前数据库的，但是不能一次获取所有的，因此写了下面的程序，跟大家共享

CREATE PROCEDURE [dbo].[GET_FILE_DTL] AS
declare @db_name nvarchar(200)
declare @sql nvarchar(200)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#TB_DB_DTL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#TB_DB_DTL]
CREATE TABLE dbo.#TB_DB_DTL (
Fileid INT,
FileGroup INT,
TotalExtents INT,
UsedExtents INT,
Name SYSNAME,
FileName NVARCHAR(500))

declare cur_getdtl scroll cursor
for
Select Name FROM Master..SysDatabases order by Name
open cur_getdtl
fetch first from cur_getdtl into @db_name
while (@@fetch_status <> -1)
begin
set @sql = 'use ' +@db_name+char(13)+'insert into #TB_DB_DTL '+'EXEC (''dbcc showfilestats'')'+char(13)
   execute sp_executesql @sql
print @sql
fetch next from cur_getdtl into @db_name
end
close cur_getdtl
DEALLOCATE cur_getdtl

select TotalExtents,UsedExtents,Name from #TB_DB_DTL
GO