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

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s