有时候会查询一下实例下所有数据库文件的可用空间,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;