[MSSQ]找出資料庫服務器上的所有資料庫佔用檔案大小

[tim]

利用 sysdatabase 及 sysfiles 來讀出所有資料庫的實體檔案大小!!

代碼: 選擇全部

create table #tmpdbsize
(
 db_name varchar(255),
 db_size int
)

declare @db_name varchar(255)
declare @db_size int
declare c cursor for select name from sysdatabases
open c
fetch next from c into @db_name
while (@@fetch_status=0)
begin
 --print @db_name
 execute('insert into #tmpdbsize select '''+@db_name+''', sum(size)*8/1024 from '+@db_name+'..sysfiles')
 fetch next from c into @db_name
end
close c
deallocate c
select * from #tmpdbsize order by db_size
drop table #tmpdbsize