Database Files Physical Location

,

Run this script from any database.

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

Rate

2.25 (4)

Share

Share

Rate

2.25 (4)