Get names of logical files in 2K

Question

Post reply

Get names of logical files in 2K

mj12

SSC-Addicted

Points: 437
More actions
October 18, 2007 at 9:58 am

#76798

I need to determine the logical names for the data and log files for a given database. The following code works for 2K5, but I need a 2K counterpart. This may not be as bullet-proof as it needs to be, but here's what I have...
SELECT
name
FROM
sys.master_files
WHERE
database_id = DB_ID('dbname goes here')
AND
(
CHARINDEX('.MDF', LOWER(physical_name)) > 0
OR
CHARINDEX('.LDF', LOWER(physical_name)) > 0
)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 1

try the sysfiles table for the database you are concerned with.

mj12 SSC-Addicted Points: 437 More actions · Answer 2

SELECT

name

FROM

master.dbo.sysfiles

only returns "master" and "mastlog" 🙁

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

[font="Courier New"]USE MyDB

SELECT name, filename FROM dbo.sysfiles[/font]

Best wishes,
Phil Factor

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

sp_HelpFile

--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)