How to List Files Inside a Folder with SQL Server

List All Folder Subdirectories in Parent Folder with SQL Server

Perhaps this will help

Declare @Table table (SubDir varchar(500),Depth int)

Insert into @Table
EXEC xp_dirtree  'C:\Foldertest',1,0

Select * From @Table

Read File Directory and enter Files into SQL Table Log but only New Files

DECLARE @FileName Table(FileList Varchar(255))

INSERT INTO @FileName  
EXEC XP_CMDSHELL 'dir "C:\Data Log\EODFiles\*.CSV" /b'

INSERT INTO  [dbo].[EOD_Log] (filename)
SELECT FileList From @FileName 
WHERE Filelist NOT IN (select * FROM [dbo].[EOD_Log])  
ORDER BY filelist desc

This should solve your NULLs problem, instead of having a variable to be left empty when there are no rows in an assignment it will simply do no INSERTs. Why even use the Top 1? And I think you don't even need to check for NULLs in FileList column.

Get each file size inside a Folder using SQL

Check this solution:

ALTER  PROCEDURE   [dbo].[GetListOfFileWithSize]  
(
    @Dir    VARCHAR(1000)
)
AS
---------------------------------------------------------------------------------------------
-- Variable decleration
---------------------------------------------------------------------------------------------
    declare @curdir nvarchar(400)
    declare @line varchar(400)
    declare @command varchar(400)
    declare @counter int

    DECLARE @1MB    DECIMAL
    SET     @1MB = 1024 * 1024

    DECLARE @1KB    DECIMAL
    SET     @1KB = 1024 

---------------------------------------------------------------------------------------------
-- Temp tables creation
---------------------------------------------------------------------------------------------
CREATE TABLE #dirs (DIRID int identity(1,1), directory varchar(400))
CREATE TABLE #tempoutput (line varchar(400))
CREATE TABLE output (Directory varchar(400), FilePath VARCHAR(400), SizeInMB DECIMAL(13,2), SizeInKB DECIMAL(13,2))

CREATE TABLE #tempFilePaths (Files VARCHAR(500))
CREATE TABLE #tempFileInformation (FilePath VARCHAR(500), FileSize VARCHAR(100))

---------------------------------------------------------------------------------------------
-- Call xp_cmdshell
---------------------------------------------------------------------------------------------    

     SET @command = 'dir "'+ @Dir +'" /S/O/B/A:D'
     INSERT INTO #dirs exec xp_cmdshell @command
     INSERT INTO #dirs SELECT @Dir
     SET @counter = (select count(*) from #dirs)

---------------------------------------------------------------------------------------------
-- Process the return data
---------------------------------------------------------------------------------------------      

        WHILE @Counter <> 0
          BEGIN
            DECLARE @filesize INT
            SET @curdir = (SELECT directory FROM #dirs WHERE DIRID = @counter)
            SET @command = 'dir "' + @curdir +'"'
            ------------------------------------------------------------------------------------------
                -- Clear the table
                DELETE FROM #tempFilePaths


                INSERT INTO #tempFilePaths
                EXEC MASTER..XP_CMDSHELL @command 

                --delete all directories
                DELETE #tempFilePaths WHERE Files LIKE '%%'

                --delete all informational messages
                DELETE #tempFilePaths WHERE Files LIKE ' %'

                --delete the null values
                DELETE #tempFilePaths WHERE Files IS NULL

                --get rid of dateinfo
                UPDATE #tempFilePaths SET files =RIGHT(files,(LEN(files)-20))

                --get rid of leading spaces
                UPDATE #tempFilePaths SET files =LTRIM(files)

                --split data into size and filename
                ----------------------------------------------------------
                -- Clear the table
                DELETE FROM #tempFileInformation;

                -- Store the FileName & Size
                INSERT INTO #tempFileInformation
                SELECT  
                        RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FilePath,
                        LEFT(files,PATINDEX('% %',files)) AS FileSize
                FROM    #tempFilePaths

                --------------------------------
                --  Remove the commas
                UPDATE  #tempFileInformation
                SET FileSize = REPLACE(FileSize, ',','')

                --------------------------------
                --  Remove the white space
                UPDATE  #tempFileInformation
                SET FileSize = REPLACE(FileSize, char(160) , '')

                --------------------------------------------------------------
                -- Store the results in the output table
                --------------------------------------------------------------

                INSERT INTO output--(FilePath, SizeInMB, SizeInKB)
                SELECT  
                        @curdir,
                        FilePath,
                        CAST(CAST(FileSize AS DECIMAL(13,2))/ @1MB AS DECIMAL(13,2)),
                        CAST(CAST(FileSize AS DECIMAL(13,2))/ @1KB AS DECIMAL(13,2))
                FROM    #tempFileInformation

            --------------------------------------------------------------------------------------------


            Set @counter = @counter -1
           END


    DELETE FROM OUTPUT WHERE Directory is null       
----------------------------------------------
-- DROP temp tables
----------------------------------------------           
DROP TABLE #Tempoutput  
DROP TABLE #dirs  
DROP TABLE #tempFilePaths  
DROP TABLE #tempFileInformation  
--DROP TABLE #tempfinal  


SELECT  * FROM  OutPut
DROP TABLE output

And guys it works!!!

How to check if filenames in specific folder are inside database table?

You need to do couple of things:

Import the file with list of files into the database.
Use cursor to go through the list of file names and match it to your table list which contains the list-b of file names

To import file name you can use the import method or directly read the file as a table virtually.

Thanks

Batch script - dir /b is including directory names and empty lines in output file

Thanks @compo ....

PUSHD \\SERVER_NAME\M$\MSSQL
FORFILES /P Backup /S /D -%NUMBER_OF_DAYS% /C "cmd /Q /D /C \"If @IsDir==FALSE For %%G In (@File) Do Echo %%~G\"" 1>K:\ScriptLogs\OutPut-%dtStamp%
POPD

This fixed the issue.

Run all SQL files in a directory

Create a .BAT file with the following command:

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
pause

If you need to provide username and passsword

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -U username -P 
password -i"%%G"

Note that the "-E" is not needed when user/password is provided

Place this .BAT file in the directory from which you want the .SQL files to be executed, double click the .BAT file and you are done!