How to return the Drives assigned to the SQL instance.

• Harold Buckner

  SSCarpal Tunnel

  Points: 4175

  More actions

  June 11, 2009 at 10:39 am

  #210844

  Is there any why to query the SQL instance and return the Drive that the instance is using. Maybe return the default database file locations and substring the the drive letter, or any other way that someone may know.

  I know EXEC master.dbo.xp_fixeddrives returns all the drive letter on the physical box but I only want to return the drive letters that the instance is dependant on.

  I'm trying to make a dynamic dw load to return all theis information and because I have mulitiple instances of SQL I get the drive letters repeated if I use the EXEC master.dbo.xp_fixeddrives procedure.

  Any ideas?

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  June 11, 2009 at 11:12 am

  #1008158

  I can think of two places you should look. The first query is for the database file locations and the second query is where the dll's are located.

  SELECT SUBSTRING(filename,1,1) as DRIVE, *

  FROM master.sys.sysaltfiles

  SELECT SUBSTRING(subsystem_dll,1,1) as DRIVE, *

  FROM msdb..syssubsystems

  WHERE subsystem_dll '[Internal]'

  This doesn't include any drives you may be sending backups to or log files. Are you concerned with those also?

• homebrew01

  SSC Guru

  Points: 55548

  More actions

  June 11, 2009 at 12:28 pm

  #1008187

  This will dump all the MDF & LDF file drive letters to a table, then you can select distinct. Probably a better way though

  CREATE TABLE dbo.DBFileLocations

  (

  DBName nvarchar(50) NULL,

  Physical_Name nvarchar(260) NULL,

  Drive_Letter nchar(1) NULL

  ) ON [PRIMARY]

  truncate table DBFileLocations

  exec sp_MSforeachdb 'use [?]

  insert into DBFileLocations

  Select ''?'' as DBName, physical_name as ''Physical_Name'', substring(physical_name,1,1) as ''drive_letter''

  from [?].Sys.database_files'