How do you know if your file exists with MSSQL?

  • In How do you know if your database exists with MSSQL? we saw how to check if the database exists as far as SQL Server is concerned. SQL Server stores all the databases names in the master database with the table sysdatabases for SQL Server 2000 and the table sys.sysdatabases for Microsoft SQL Server 2005 and Microsoft SQL Server 2008.
  • Yes, but does the actual file exists? Microsoft provides an undocumented extended stored procedure: xp_fileexist, that allows us to check for the existence of any file, including the directories.
  • Undocumented means that Microsoft will not support any problem related to xp_fileexist.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

xp_fileexist

  1. SELECT SUBSTRING(name,1,30) AS 'Database',
  2.        crdate AS 'Created',
  3.        SUBSTRING(filename,1,95) AS 'Disk file'
  4. FROM sys.sysdatabases
  5. WHERE name = 'sql911';
  6. go
[Get Code]
Database                       Created                 Disk file
------------------------------ ----------------------- -----------------------------------------------------------------------------------------------
sql911                         2007-10-22 09:30:51.020 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf

(1 row(s) affected)
  • The sql911 database uses the file: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf
  • Does it really exist on disk? Or did somebody erase the file?
  • Note: Line 4: FROM sys.sysdatabases' needs to be replaced with FROM sysdatabases for MSSQL 2000.
  1. EXEC master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf';
  2. go
[Get Code]
File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
1           0                   1

(1 row(s) affected)
  • The file does exists and it's not a directory, it's a real file.
  • xp_fileexist also supports output of data:
  1. DECLARE @i INT
  2. EXEC master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf', @i OUTPUT;
  3. SELECT @i AS 'File exists'
  4. go
[Get Code]
File exists
-----------
1

(1 row(s) affected)