This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Quick T-SQL Tip: Parsing Drive and File Information in SQL Server

Parsing full physical file names for information is one of those tasks a database administrator (DBA) finds they do on a semi-frequent basis; from looking up SQL Agent Job output paths or reviewing database file metadata. Recently, I found myself updating a SQL Server Reporting Services (SSRS) report which provides SQL Agent Job failure history to the DBA team. I was adding functionality that would include the output file for any job hitting the report. This allows the DBA to troubleshoot the failure details without having to navigate to the job on the instance and pull up the location of the file, THEN open the file in notepad. Single-click on the hyperlink of the output file in the new report and the file opens automatically.

Or at least that was the idea. . .

What I found out was that if there were spaces in the file name the hyperlink would not get generated. Therefore, there was a need to identify those file names with spaces and convert them to underscores (_). The other issue was that I wanted to be able to employ that single-click philosophy. In order to do so I had to convert each file stored as a local path to a Universal Naming Convention (UNC).

Helpful Parsing Query

In order to do these tasks, I had to identify what files needed to be touched, which in turn led to the following parsing query I'm sharing with you today:

These are the results when run as a test against the sys.database_files System Catalog View. The process works against any column storing a full physical file path, however, as is shown when I put it to actual use against msdb.dbo.sysjobsteps: