Db files size per directory

/************************************************************************
*
* Author: Rafal Skotak
* Purpose: Procedure displays amount of disk space used by databases per directories
* Date: 2008-01-14
*
************************************************************************/
if exists(select * from sysobjects where id = object_id('dbo.proc_get_db_files_size') and xtype = 'P')
drop procedure dbo.proc_get_db_files_size
go

create procedure dbo.proc_get_db_files_size
as
begin
set nocount on

create table #temp_result_table
(
[rec_id] int identity(1, 1) primary key,
[db_name] sysname not null,
[files_size] int not null,
[directory] nvarchar(1024) not null
);

create table #temp_dbs_table
(
[db_name]sysname not null primary key,
);

insert into #temp_dbs_table ([db_name]) select [name] from master..sysdatabases

declare @db_name sysname

set @db_name = ''

while @db_name is not NULL
begin
set @db_name = NULL

select @db_name = [db_name] from #temp_dbs_table

if @db_name is NULL
break

declare @n_cmd nvarchar(4000)

set @n_cmd = 'insert into #temp_result_table ([db_name], [files_size], [directory]) select ''' + @db_name + ''', size * 8, reverse(ltrim(rtrim(filename))) from [' + @db_name + ']..sysfiles'

exec sp_executesql @n_cmd

delete from #temp_dbs_table where [db_name] = @db_name
end

drop table #temp_dbs_table

update 
#temp_result_table 
set 
[directory] = reverse
(
substring
(
[directory], charindex('\', [directory], 0), len([directory])
)
)

select 
[db_name], 
[directory], 
sum([files_size]) as [files_size] 
from 
#temp_result_table
group by
[db_name],
[directory]
order by
[db_name],
[directory]

drop table #temp_result_table
end
go

-- example :

exec dbo.proc_get_db_files_size