Sql Query to check file exists

I recently came across the following usefull SQL query, Maybe you’ll find it useful.


declare @file_path	nvarchar(500)
declare @file_exists	int

set @file_path = 'C:\Temp.txt'

exec master.dbo.xp_fileexist 
	@file_path,
	@file_exists output 
Print	'File '+isnull(@file_path,'NULL')+' '+
	case when @file_exists = 1
	then 'exists'
	else 'does not exist'
	end

This actually check existence of file C:\Temp.txt

Result:
File C:\Temp.txt exists

You can also use above query to check file Temp.txt file is exist. If YES Delete table or what ever…
Here is modified Query.

declare @file_path	nvarchar(500)
declare @file_exists	int

set @file_path = 'C:\temp.txt'

exec master.dbo.xp_fileexist 
	@file_path,
	@file_exists output 
	
IF @file_exists = 1
BEGIN
    DELETE FROM [Ashish].[dbo].[Temp]
END

Thanks.

Leave a Reply