-- Variables
declare 
	@xDatabase	varchar(50),
	@xSQL		nvarchar(2048)

set @xSQL = ''

-- Determine free space on drives
create table #drives(
	drive char(1),
	freespace varchar(12) null
)

create table #info(
	[Database]	varchar(128),
	[Filename]	varchar(256),
	[Size]		int,
	[FreeSpace]	int
)

insert into
	#drives
exec
	master..xp_fixeddrives

-- Server name
-- select srvname from master..sysservers where isremote = 0 and dist = 0

-- List of databases
declare db_cursor cursor for
	select distinct
		name
	from
		master..sysdatabases
	where
		name not in('master', 'tempdb', 'model', 'msdb', 'pubs', 'Northwind')
for read only

open db_cursor

fetch next from
	db_cursor
into
	@xDatabase

while(@@fetch_status = 0)
begin
	set @xSQL = 
		'insert into #info select ''' + @xDatabase + ''' [Database], ' +
			'convert(varchar(50), filename) [Filename], ' +
			'(size * 8)/1024 Size, (select convert(int,freespace) from #drives where drive = left(filename, 1) collate SQL_Latin1_General_CP1_CI_AS) ' +
		'from [' + @xDatabase + ']..sysfiles'

	exec (@xSQL)

	fetch next from
		db_cursor
	into
		@xDatabase
end

select * from #info

deallocate db_cursor
drop table #drives
drop table #info