1、新建一個(gè)臨時(shí)數(shù)據(jù)庫(kù)，用于存放查詢結(jié)果
CREATE TABLE DatabaseFileLog
(
date DATETIME,
dbname VARCHAR(20),
FILENAME VARCHAR(100),
fileSIZE FLOAT
)
2、通過游標(biāo)遍歷所有數(shù)據(jù)庫(kù)
declare @dbName varchar(50)
declare @command varchar(1024)
declare dbName_cursor CURSOR FOR
select [name]
from master.dbo.sysdatabases
where [name] not in ('master','tempdb','msdb','model')
open dbName_cursor
FETCH NEXT FROM dbName_cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
begin
set @command = '
insert into DatabaseFileLog
select
getdate(),
'''+
@dbName
+''',
filename,
convert(float,size) * (8192.0/1024.0)/1024.0 as ''MB''
from '+@dbName +'.dbo.sysfiles ';
exec ( @command );
FETCH NEXT FROM dbName_cursor INTO @dbName ;
end
CLOSE dbName_cursor;
DEALLOCATE dbName_cursor;
3、查詢結(jié)果
SELECT * FROM DatabaseFileLog