Logical File Names

• Carl Farmer

  SSCrazy

  Points: 2197

  More actions

  August 12, 2003 at 1:48 pm

  #80848

  Does anyone have any ideas on a query that I can run to retrieve all logical file names (including path names) for a list of DB's? It needs to include both DB and TXLOG.

  Thank you.

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  August 12, 2003 at 2:14 pm

  #469650

  declare @dbname varchar(50)

  declare dbname cursor

  for select name from master..sysdatabases

  open dbname

  fetch next from dbname into @dbname

  while @@fetch_status = 0

  begin

  select @message = 'select name, filename from ' + @dbname + '..sysfiles'

  execute (@message)

  fetch next from dbname into @dbname

  end

  deallocate dbname

  Edited by - allen_cui on 08/12/2003 2:15:32 PM

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  August 13, 2003 at 12:03 am

  #469673

  Hi Carl,

  quote:

  ---

  Does anyone have any ideas on a query that I can run to retrieve all logical file names (including path names) for a list of DB's? It needs to include both DB and TXLOG.

  ---

  another way could be

  sp_MSforeachdb @command1 = "use ? select name, filename from sysfiles"

  Cheers,

  Frank

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]