Directory Path for tempdb

Question

I am trying to find out how to just get the directory of the tempdb. I found this query
SELECT filename FROM tempDB.sys.sysfiles;

When I run this query, I get this in one column
G:\Program Files\Microsoft SQL Server\MSSQL15.DEV01\MSSQL\Data\tempdb.mdf

I just want to get
G:\Program Files\Microsoft SQL Server\MSSQL15.DEV01\MSSQL\Data\

How do I substring this to get just the directory path and not include the file name.

For example, I found this InstanceDefaultBackupPath, to find the directory path for the Instance Default Backup Path. What about something like that for the tempdb default backup path?

Answer 1

Hi @LauraC
Try this query:

SELECT DISTINCT REVERSE(SUBSTRING(REVERSE(filename),CHARINDEX('\',Reverse(filename))+1,LEN(filename))) AS FilePath  
FROM tempDB.sys.sysfiles;  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Please see:

https://www.red-gate.com/simple-talk/blogs/extract-the-path-from-filename-in-sql/

Answer 3

Hi @LauraC ,

Here is the easiest way.

SQL

SELECT filename  
	, directory = REPLACE(filename, 'tempdb.mdf', '')  
FROM tempDB.sys.sysfiles  
WHERE name = 'tempdev';