Database Files Physical Location

SET NOCOUNT ON
declare @db_list table( row_no smallint identity (1,1), db varchar(200))

INSERT into @db_list
select name from master..sysdatabases

declare @first smallint
declare @last smallint
declare @db varchar(200)
declare @sql varchar(500)

select @first = min(row_no) from @db_list
select @last = max(row_no) from @db_list

create table #db_file_list ( db_name varchar(100), Filename varchar(200), file_location varchar(500))

while @first <= @last
BEGIN

select @db = db from @db_list where row_no = @first

SET @sql = 'INSERT INTO #db_file_list select '+ CHAR(39) + @db + CHAR(39)+' ,name,filename from '+ @db+'..sysfiles '

--print (@sql)
exec (@sql)

SET @first = @first + 1

END

--select * from #db_file_list where file_location like '%N:%'

select * from #db_file_list

drop table #db_file_list

SET NOCOUNT OFF