How can I verify that a file exists in Windows with SQL?

I have a SQL Server running on my Windows Server and, at a specific column of a table, I have the path for a Zip file (which in turn has the source of the data stored in the database). Some of these are not valid (do not match the data in database). I need to make SQL Server verify that these Zip files exist and that they match the column that stores the path and name of the zip file. This way I will delete the wrong file-path column correspondences开发者_JS百科.

you can use the undocumented proc xp_fileexist will return 1 if it exists and 0 otherwise

SET NOCOUNT ON
DECLARE @iFileExists INT

EXEC master..xp_fileexist 'c:\bla.txt', 
 @iFileExists OUTPUT

select @iFileExists

You can use xp_fileexist however please note it is undocumented and unsupported.

You can use SQLCLR, however you didn't bother specifying what version of SQL Server you're using, so it may not be relevant - and in any case it is disabled by default, and security policies prevent its use in some places.

You can use a #temp table and xp_cmdshell, however xp_cmdshell is typically disabled for the same reasons as SQLCLR.

/* if you need to enable xp_cmdshell:

exec master..sp_configure 'show adv', 1;
reconfigure with override;
exec master..sp_configure 'xp_cmdshell', 1;
reconfigure with override;
exec master..sp_configure 'show adv', 0;
reconfigure with override;

*/

SET NOCOUNT ON;

DECLARE 
   @file VARCHAR(1000),
   @path VARCHAR(255),
   @cmd  VARCHAR(2048);

SELECT
   @file = 'foo.zip',
   @path = 'C:\wherever\';

SELECT @cmd = 'dir /b "' + @path + @file + '"';

CREATE TABLE #x(a VARCHAR(1255));
INSERT #x EXEC master..xp_cmdshell @cmd;
IF EXISTS (SELECT 1 FROM #x WHERE a = @file)
   PRINT 'file exists';
ELSE
   PRINT 'file does not exist';
DROP TABLE #x;

EDIT based on new requirements. It shows a list of files either in the table or in the database, and indicates whether the file is in only one location or both. It assumes that path + file is <= 900 characters long (merely to be able to use an index on at least one side).

USE tempdb;
GO

CREATE TABLE dbo.files(f VARCHAR(1000));

INSERT dbo.files(f) SELECT 'zip_that_does_not_exist.zip'
    UNION ALL SELECT 'c:\path\file_that_does_not_exist.zip'
    UNION ALL SELECT 'c:\path\file_that_exists.zip'
    UNION ALL SELECT 'zip_that_exists.zip';

DECLARE 
   @path VARCHAR(255),
   @cmd  VARCHAR(2048);

SELECT
   @path = path_column,
   @cmd  = 'dir /b "' + path_column + '"'
FROM 
   dbo.table_that_holds_path;

CREATE TABLE #x(f VARCHAR(900) UNIQUE);

INSERT #x EXEC master..xp_cmdshell @cmd;
DELETE #x WHERE f IS NULL;
UPDATE #x SET f = LOWER(f);

WITH f AS 
(
    SELECT f = REPLACE(LOWER(f), LOWER(@path), '')
        FROM dbo.files
)
SELECT 
    [file] = COALESCE(x.f, f.f),
    [status] = CASE 
        WHEN x.f IS NULL THEN 'in database, not in folder'
        WHEN f.f IS NULL THEN 'in folder, not in database'
        ELSE 'in both' END
FROM
    f FULL OUTER JOIN #x AS x
    ON x.f = f.f;

DROP TABLE #x, dbo.files;