|
if object_id('tempdb..#tmp_filesize') is not null
drop table #tmp_filesize
GO
create table #tmp_filesize
(
server_name varchar(256),
database_name varchar(256),
file_group varchar(256),
logical_name varchar(256),
physical_name varchar(1024),
type_desc varchar(128),
used_size_mb float,
allocated_size_mb float,
max_size_mb float,
disk_free_mb float,
free_space_percent float,
growth int,
is_percent_growth int
)
GO
exec sp_msforeachdb 'use [?]
insert into #tmp_filesize
select @@SERVERNAME as server_name
,DB_NAME() as database_name
,case when data_space_id = 0 then ''LOG''
else FILEGROUP_NAME(data_space_id)
end as file_group
,name as logical_name
,physical_name
,type_desc
,FILEPROPERTY(name,''SpaceUsed'')/128.0 as used_size_mb
,size/128.0 as allocated_size_mb
,case when max_size = -1 then max_size
else max_size/128.0
end as max_size_mb
,vs.available_bytes/1024.0/1024 as disk_free_mb
,case when growth = 0 then (size - FILEPROPERTY(name,''SpaceUsed''))*1.0/size
when growth > 0 and max_size = -1 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,''SpaceUsed'')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,''SpaceUsed'')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) < 0 then (max_size - FILEPROPERTY(name,''SpaceUsed''))*1.0/max_size
else null
end as free_space_percent
,growth
,CAST(is_percent_growth as int) as is_percent_growth
from sys.database_files df
cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs
where state_desc = ''ONLINE'''
select * from #tmp_filesize
| 
