search mdb files in specific path

• Vahid.Ch

  SSC-Addicted

  Points: 424

  More actions

  March 3, 2012 at 11:00 pm

  #257823

  hi there,I want to search mdb files in specific path.

  following query select all of files in "..\Microsoft SQL Server\MSSQL10_50.SPR2\MSSQL\DATA\.." path.

  SELECT name, physical_name AS current_file_location

  FROM sys.master_files

  but how can I select different path? for instance c:\Db\...

  something like this:

  SELECT name, physical_name AS current_file_location

  FROM (c:\Db\..)

  any idea?

• manukumar

  SSC-Addicted

  Points: 489

  More actions

  March 3, 2012 at 11:16 pm

  #1454512

  Vahid.Ch (3/3/2012)

  ---

  hi there,I want to search mdb files in specific path.

  following query select all of files in "..\Microsoft SQL Server\MSSQL10_50.SPR2\MSSQL\DATA\.." path.

  SELECT name, physical_name AS current_file_location

  FROM sys.master_files

  but how can I select different path? for instance c:\Db\...

  something like this:

  SELECT name, physical_name AS current_file_location

  FROM (c:\Db\..)

  any idea?

  If I am not wrong try where condition with like keyword to search the file that you want.

  SELECT name, physical_name AS current_file_location

  FROM sys.master_files where name like '%C:\db%'

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  March 4, 2012 at 11:04 am

  #1454580

  Vahid.Ch (3/3/2012)

  ---

  hi there,I want to search mdb files in specific path.

  following query select all of files in "..\Microsoft SQL Server\MSSQL10_50.SPR2\MSSQL\DATA\.." path.

  SELECT name, physical_name AS current_file_location

  FROM sys.master_files

  but how can I select different path? for instance c:\Db\...

  something like this:

  SELECT name, physical_name AS current_file_location

  FROM (c:\Db\..)

  any idea?

  sys.master_files doesnt' read from the operating system. It's simply a list of where all the database files are.

  To find files not in this list, you'll need to use a) a DOS DIR /s command, b) a Powershell routine, c) a CLR, or d) some other method.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)