How to get the logical and physical file names for a database
For some operations you need to know the logical and physical file names for the data and log files in a database. Here's some code to get hold of this information. It assumes you only have one data and one log file. This is all I have needed so far. I'm sure it could be adapted fairly easily to return a set of file names.
declare @DBName sysname
, @LogicalDataFile sysname
, @LogicalLogFile sysname
, @PhysicalDataFile nvarchar(260)
, @PhysicalLogFile nvarchar(260)
set @DBName = '<database-name>'
-- Data file
select @LogicalDataFile = name
, @PhysicalDataFile = physical_name
from sys.master_files
where database_id = db_id(@DBName)
and type_desc = 'ROWS'
-- Log file
select @LogicalLogFile = name
, @PhysicalLogFile = physical_name
from sys.master_files
where database_id = db_id(@DBName)
and type_desc = 'LOG'
select @LogicalDataFile as [@LogicalDataFile]
, @LogicalLogFile as [@LogicalLogFile]
, @PhysicalDataFile as [@PhysicalDataFile]
, @PhysicalLogFile as [@PhysicalLogFile]
Note: This is the revised version. Thanks to steveh99999 for gently pointing out I was using a deprecated system table. A nice side effect is that there's no need for dynamic SQL either.