I am running the following statement in SQL Server 2014(in Windows 2012R2):

select filename from dbo.sysfiles where fileid = 1

However it returns:


I wish for the full path without the tilde.

(e.g C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\master.mdf)

I tried upgrading the SQL Server 2014 Express to 2016 Express but it made no difference.

Any ideas? Thanks. Dave

  • Time to read the documentation - this is an obsolete system table (now view) – SMor Feb 8 at 21:48

If you use the non-deprecated equivalent sys.database_files view's column physical_name, does it provide you the full path?

  • I will try it when I go back as it's not my server, thanks for the info. The main problem is this is running embedded in some c# code so I wanted to avoid rebuilding/re-releasing the code and wanted to know why the tilde was appearing as it does not appear with this on any other SQL instance I have tried – dave Feb 9 at 8:28
  • 1
    FWIW, I tried your query and I did not get the short name. I.e., I'm not convinced that this is because you use sysfiles. Still, don't want to use the old views, but just as a FWIW. So, first step would indeed be to see what sys.database_files says. Long or short name, and then take it from there. (The OS version might be a factor here. My thinking is that perhaps with older os sysfiles show short name, but short names aren't in newer os? Thinking out loud here...) – Tibor Karaszi Feb 9 at 9:33
  • Yes i was thinking it was possibly O/S. There is something on the server I think, as I have tried this on another Windows 2012R2 and a Windows 2008 R2 server and it works perfectly (shows the long name) – dave Feb 9 at 10:09
  • TiborKaraszi I concur, I also didn't run into the issue with the deprecated view but don't know enough about the OS side to be able to speculate what is going on. @dave If you right click on your database (in SSMS) and then click Properties and go to the Files page, what do the file paths look like in there for your database files?...Perhaps this shortcut file path was used during initial creation of the database?...if so, perhaps you can carefully change the path to the fully qualified path in the database properties' Files page, and it'll solve your problem? – J.D. Feb 9 at 13:00
  • Thanks guys, I will give that a go, however I am not there now until next week, I have also been looking into Windows Short (8.3) Filenames issues and I will also try and disable them and remove them as per this link: acunetix.com/blog/articles/… – dave Feb 9 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.