Check if File/Folder exists

drop table #XpFileExists

CREATE TABLE #XpFileExists         

(         

 IsFileExists bit,         

 IsFileDirectory bit,            

 IsParentDirectoryExists bit         

)         

drop table #Path

CREATE TABLE #Path

(

       [FilePath] [varchar](100) NOT NULL,

       [IsFileExists] [bit] NULL,

       [IsFileDirectory] [bit] NULL,

       [IsParentDirectoryExists] [bit] NULL,

       [IsActive] [bit] NOT NULL,

       [UpdatedOn] [datetime] NULL,

       [CreatedOn] [datetime] NOT NULL,

)

        

declare @FilePath [varchar](500)         

         

declare @IsFileExists bit         

declare @IsFileDirectory bit         

declare @IsParentDirectoryExists bit         

     

declare @FileCounter table (FilePath varchar(500))     

   

insert into @FileCounter(FilePath)

values

('C:\Share\'),

('D:\Share')

while exists(select top 1 * from @FileCounter)         

begin         

         

 select top 1 @FilePath = FilePath from @FileCounter         

          

         

 delete from #XpFileExists         

          

 insert into #XpFileExists         

 Exec master.dbo.xp_fileexist @FilePath         

         

          

 select         

  @IsFileExists = IsFileExists,         

  @IsFileDirectory = IsFileDirectory,            

  @IsParentDirectoryExists = IsParentDirectoryExists         

 from #XpFileExists 

 select * from #XpFileExists       

         

 update b set          

  IsFileExists = @IsFileExists,         

  IsFileDirectory = @IsFileDirectory,            

  IsParentDirectoryExists = @IsParentDirectoryExists,     

  UpdatedOn = GETDATE()     

 from #Path b         

 where b.FilePath = @FilePath         

      

 delete from @FileCounter where FilePath = @FilePath     

      

end         

     

select *     

from #Path     

where IsFileDirectory = 0     

and IsActive = 1