Check File/Folder Exists —undocumented extended stored procedures:xp_DirTree, xp_subdirs and xp_fileexist



  
  
CREATE TABLE #SubDirTree (folder varchar(200), IsFolder bit )

 INSERT INTO #SubDirTree (folder,  IsFolder)

EXEC Master.dbo.xp_DirTree 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL',1
 
 Select * FROM #SubDirTree 
 --where folder = 'Backup'

 drop table #SubDirTree

  
CREATE TABLE #DirTree (folder varchar(200), IsFolder bit )

 INSERT INTO #DirTree (folder,  IsFolder)

EXEC Master.dbo.xp_DirTree 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL',1 
 Select * FROM #DirTree 
 --where folder = 'Backup'

 drop table #DirTree

 
CREATE TABLE #folders (FileExists int, IsDirectory int, ParentDirExists int) 
INSERT INTO #folders 
EXEC master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL' 
IF EXISTS(SELECT IsDirectory FROM #folders WHERE IsDirectory=1) 
PRINT 'MSSQL exists' 
ELSE 
PRINT 'MSSQL not exists' 
DROP TABLE #folders
 
DECLARE @pathName varchar(255)
CREATE TABLE #folder (
File_Exists int,
File_is_a_Directory int,
Parent_Directory_Exists int
)
SELECT @pathName='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL'
INSERT INTO #folder
(File_Exists, file_is_a_directory, parent_directory_exists)
EXEC Master.dbo.xp_fileexist @pathName
SELECT * FROM #folder
DROP TABLE #folder
GO


CREATE TABLE #folders (DirCol varchar(100))

 INSERT INTO #folders
 EXEC master.dbo.xp_subdirs 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL'

 Select * FROM #folders
 -- where DirCol = 'Backup'


 drop table #folders


--EXEC master.dbo.xp_subdirs @pathName
--EXEC Master.dbo.xp_fileexist @pathName
--EXEC Master.dbo.xp_DirTree @pathName,1
   

 
Advertisements

2 Comments on “Check File/Folder Exists —undocumented extended stored procedures:xp_DirTree, xp_subdirs and xp_fileexist”

  1. Roger K says:

    i am tring to run this sql to check if a folder exists.

    CREATE TABLE #folders (DirCol varchar(100))
    INSERT INTO #folders
    EXEC master.dbo.xp_subdirs @SumDatPath

    where @SumDatPath is a invalid path to test if it exists.

    I am getting this error

    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discarded.

    If the folder exists I do not get an error. I am sure this was working about a month ago as I have the code in another stored procedure. I was adding it to another proc and it now gives me this error.

    • Jingyang Li says:
       
       DECLARE @somePath VARCHAR(255)
       
      SET @somePath = 'DIR "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL" /B'
      CREATE TABLE #DirectoryExists (IsValid VARCHAR(MAX))
      INSERT INTO #DirectoryExists
      EXEC xp_cmdshell @somePath
      
       
      
       
      DECLARE @IsThere INT = 0
      SELECT @IsThere =  (SELECT COUNT(*) FROM #DirectoryExists
            WHERE IsValid <> 'File Not Found' AND IsValid IS NOT NULL)
       
       Drop table #DirectoryExists
      
      IF @IsThere > 0
      
      Begin
      
      CREATE TABLE #folders (DirCol varchar(100))
      
       INSERT INTO #folders
       EXEC master.dbo.xp_subdirs 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL'
      
       Select * FROM #folders
       
       drop table #folders
      
       End
      
       
         
       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s