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 = '' -- 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.