drop table #tmp_database
select rn=row_number() over(order by name desc)
,[name]
into #tmp_database
from sys.databases
where [name] not in ('master','model','msdb','DWDiagnostics','DWQueue')
declare @i int,
@row_number int,
@sql varchar(max)
set @i=1
select @row_number = rn from #tmp_database where rn=@i
while @i = @row_number
begin
select [name]
from #tmp_database
where rn=@i
set @i=@i+1
set @sql = 'use ' + @i
exec (@sql)
insert into OperationDB.dbo.databas_file (name,Size_mb,used_mb,free_mb,Pctfree,type_desc)
select f.[name],
Size_mb=(f.size)/128,
Used_mb=(fileproperty(f.name,'spaceused')) /128,
Free_mb=(f.size-fileproperty(f.name,'spaceused'))/128,
Pctfree=(f.size-fileproperty(f.name,'spaceused')) *100.0/f.size,
[type_desc]
From sys.database_files f
drop table #tmp_database select rn=row_number() over(order by name desc) ,[name] into #tmp_database from sys.databases where [name] not in ('master','model','msdb','DWDiagnostics','DWQueue')
declare @i int, @row_number int, @sql varchar(max) set @i=1 select @row_number = rn from #tmp_database where rn=@i
while @i = @row_number begin
select [name] from #tmp_database where rn=@i
set @i=@i+1
set @sql = 'use ' + @i exec (@sql) insert into OperationDB.dbo.databas_file (name,Size_mb,used_mb,free_mb,Pctfree,type_desc) select f.[name], Size_mb=(f.size)/128, Used_mb=(fileproperty(f.name,'spaceused')) /128, Free_mb=(f.size-fileproperty(f.name,'spaceused'))/128, Pctfree=(f.size-fileproperty(f.name,'spaceused')) *100.0/f.size, [type_desc] From sys.database_files f