查询所有数据库文件的可用空间

最新推荐文章于 2023-04-14 09:30:00 发布

baicongli9053

最新推荐文章于 2023-04-14 09:30:00 发布

阅读量97

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/Joe-T/archive/2013/03/28/2986776.html

版权

有时候会查询一下实例下所有数据库文件的可用空间，SSMS上没有汇总的地方，仪表盘又只日志文件的可用空间，于是写了一个SP自己用.

/**
 author:Joe.TJ
 date:20130328
 desc:get the free space of database file
**/
create proc usp_GetFreeSpace
as
set nocount on;
declare csr cursor for select name  from sys.databases where database_id>4;
declare @cmd varchar(2000);
declare @db varchar(50);
create table #res([db_name] varchar(50),[file_name] varchar(50),current_size_mb float,free_space_mb float);
open csr
fetch next from csr into @db;
while(@@FETCH_STATUS=0)
begin
set @cmd=
'use '+@db+
' select DB_NAME() as db_name,name as file_name,
size/128.0 as current_size_mb,(size-FILEPROPERTY(name,''SpaceUsed''))/128.0 as free_space_mb
from sys.database_files';
insert into #res
exec(@cmd);
fetch next from csr into @db;
end
close csr;
deallocate csr;
select * from #res;
drop table #res;
set nocount off;