1、新建一个临时数据库，用于存放查询结果
CREATE TABLE DatabaseFileLog
(
date DATETIME,
dbname VARCHAR(20),
FILENAME VARCHAR(100),
fileSIZE FLOAT
)
2、通过游标遍历所有数据库
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、查询结果
SELECT * FROM DatabaseFileLog