Date: 03/10/11 (SQL Server)    Keywords: database

 We create a database called ADMIN which stores our administrative information such as file space statistics. We use a combination of extended stored procedures and publicly-available code to log these statistics. Here is a samples:

/* Get current space statistics. You can run this and store the results in a holding table. */

CREATE PROCEDURE [dbo].[sp_file_space] @server_name sysname, @id int
as

declare @dbname sysname
declare @cmd varchar(700),
@lname_len int,
@fname_len int,
@fgroup_len int

set nocount on

select @cmd = 'use [?] select ' + convert(varchar,@id) + ',''' + rtrim(@server_name) + ''',db_name(),logical_name = name,fileid,upper(substring(filename,1,1)),filename,
filegroup = filegroup_name(groupid),
size_in_KB = size * 8,
maxsize_in_KB = (case maxsize when -1 then 0
else
maxsize * 8 end),
growth = (case status & 0x100000 when 0x100000 then growth else growth * 8 end),
KB_growth_flag = (case status & 0x100000 when 0x100000 then 0 else 1 end) ,
usage = (case status & 0x40 when 0x40 then ''Log Only'' else ''Data Only'' end)
from sysfiles order by fileid'


exec sp_MSforeachdb @command1=@cmd

return 0


GO

** Please be responsible with free code. Test and check before implementing in a production environment

Source: https://sqlserver.livejournal.com/73512.html