How to read list of files in folder using T-SQL?

  • I have used this original post back then to write a script to scan a specified folder for files with .sql extension, execute them across all test DB's we have, attach to an email and to remove from that folder.
    Perhaps it will save you some time:

    DECLARE @sql_files_path        VARCHAR(100) = '\\YourServer\c$\Temp\SQLScripts\',
            @file_name            VARCHAR(100),
            @db_name            VARCHAR(50),
            @CommandString        VARCHAR(500),
            @return_value        INT,
            @error_message        NVARCHAR(1000) = '',
            @email_address  VARCHAR(1000) = 'yourEmail@Organisation.com',
            @email_subject  NVARCHAR(1000),
            @email_body   NVARCHAR(MAX),
            @number_of_records INT,
            @delete_file   VARCHAR(1000);

    SET @email_subject = 'Automated Processing Results of SQL Files on Server '+@@SERVERNAME + ' Scanned and Loaded From Folder '+@sql_files_path;

    --==== START Create temp table to hold error messages =====--
    IF OBJECT_ID('tempdb..#error_messages') IS NOT NULL
      DROP TABLE #error_messages;
    CREATE TABLE #error_messages (err_message NVARCHAR(500));
    --******* END *********---

    --==== START Create temp table to hold final messages =====--
    IF OBJECT_ID('tempdb..#final_messages') IS NOT NULL
      DROP TABLE #final_messages;
    CREATE TABLE #final_messages (final_message NVARCHAR(MAX));
    --******* END *********---

    --==== START Get list of SQL files within the above folder =====---
    IF OBJECT_ID('tempdb..#directoryTree') IS NOT NULL
      DROP TABLE #directoryTree;

    CREATE TABLE #directoryTree (
       id                INT IDENTITY(1,1),
       SQL_file_name    NVARCHAR(500),
       depth            INT,
       is_file            BIT);

    INSERT INTO #directoryTree
    EXEC xp_dirtree @sql_files_path, 1, 1;

    DELETE FROM #directoryTree
    WHERE is_file = 0
    OR  SQL_file_name NOT LIKE '%.sql'
    --******** END **********--

    --=== Use cursor to process files one by one. ====---
    DECLARE OSQL_cursor CURSOR
    READ_ONLY FOR
    SELECT SQL_file_name
    FROM #directoryTree
    ORDER BY SQL_file_name

    OPEN OSQL_cursor

    FETCH NEXT FROM OSQL_cursor INTO @file_name
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN

     --=== Here comes a new cursor to change DB's
     DECLARE db_cursor CURSOR
        READ_ONLY FOR
        SELECT name
         FROM master.dbo.sysdatabases
         WHERE dbid > 4
         AND name NOT LIKE '%MCF%'
         AND name NOT LIKE '%TFS%'
         AND name NOT LIKE '%Notes%'

         OPEN db_cursor
         FETCH NEXT FROM db_cursor INTO @db_name
         WHILE (@@FETCH_STATUS <> -1)
         BEGIN -- start of db_cursor

       SET @CommandString = 'osql -S ' + @@ServerName + ' -E -n -b -d ' + @db_name + ' -i "' + @sql_files_path + @file_name + '"';
            DELETE FROM #error_messages;
            INSERT INTO #error_messages
       EXEC @return_value = master.dbo.xp_cmdshell @CommandString;
            
       IF (@return_value = 0)
       BEGIN
              INSERT INTO #final_messages
        VALUES( 'Successfully applied ' + @sql_files_path+@file_name + ' on ' + @db_name);
        
       END
       ELSE
       BEGIN
             
        --********* 'Error Handling from here: http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/how-to-capture-error-output-from-xp_cmds/'
                SELECT @error_message = @error_message + err_message
                 FROM #error_messages
                 WHERE err_message IS NOT NULL;

                INSERT INTO #final_messages
                VALUES(' Database '+@db_name+'. File name '+@sql_files_path+@file_name+' '+@error_message);

                DELETE FROM #error_messages;
                SET  @error_message = '';
       END
            FETCH NEXT FROM db_cursor INTO @db_name
      END; --- end db_cursor
      CLOSE db_cursor;
      DEALLOCATE db_cursor;

    --just before moving to the next sql file, delete current one from the folder
    SET @delete_file = 'del '+@sql_files_path+@file_name;
    EXEC @return_value = master.dbo.xp_cmdshell @delete_file;

    FETCH NEXT FROM OSQL_cursor INTO @file_name
     END

    CLOSE OSQL_cursor;
    DEALLOCATE OSQL_cursor;

    --- Anything was processed?
    SELECT @number_of_records = COUNT(*) FROM #final_messages
    IF @number_of_records > 0
    BEGIN

    SET @email_body = N'<style type="text/css">
    #box-table
    {
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 12px;
    text-align: center;
    border-collapse: collapse;
    border-top: 7px solid #039;
    border-bottom: 7px solid #039;
    }
    #box-table th
    {
    font-size: 13px;
    font-weight: normal;
    background: #fff;
    border-right: 2px solid #039;
    border-left: 2px solid #039;
    border-bottom: 2px solid #039;
    color: #039;
    }
    #box-table td
    {
    border-right: 1px solid #aabcfe;
    border-left: 1px solid #aabcfe;
    border-bottom: 1px solid #aabcfe;
    color: #669;
    text-align: left
    }
    tr:nth-child(odd)    { background-color:#eee; }
    tr:nth-child(even)    { background-color:#fff; }    
    </style>'+    
    N'<H3><font color="Red"> Below is a result of processed files.' +
    N'<table id="box-table" cellpadding="10" >' +
    N'<tr><font color="Green"><th>Result</th>
    </tr>' +

    CAST ( (

    SELECT td = [final_message]
    FROM #final_messages
    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail
      @recipients    = @email_address,
         @subject     = @email_subject,
         @body      = @email_body,
      @body_format = 'HTML' ;
        

    END;

  • I know this was a followup to an old SQL2008 question, but, if you're running SQL 2017, check out the new DMV `sys.dm_os_enumerate_filesystem`

    eg:

    SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Office','*')

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • ThomasRushton - Monday, June 25, 2018 7:20 AM

    I know this was a followup to an old SQL2008 question, but, if you're running SQL 2017, check out the new DMV `sys.dm_os_enumerate_filesystem`

    eg:

    SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Office','*')

    It's an awesome tool.  I wish they'd back port some of this stuff to 2016.  I also wish they'd add a BULK EXPORT, support for true CSV and TSV files, and some file handling.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Monday, June 25, 2018 8:08 AM

    ThomasRushton - Monday, June 25, 2018 7:20 AM

    I know this was a followup to an old SQL2008 question, but, if you're running SQL 2017, check out the new DMV `sys.dm_os_enumerate_filesystem`

    eg:

    SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Office','*')

    It's an awesome tool.  I wish they'd back port some of this stuff to 2016.  I also wish they'd add a BULK EXPORT, support for true CSV and TSV files, and some file handling.

    May be awesome, but still adding no value to the product.
    It's may appear useful only to those who believe in false advertising of security issues around xp_cmdshell.
    Sadly, the array of those believers includes MS developers themselves.

    They'd better spend their effort on introducing "BIG FLOAT" data type.
    It's a shame they allow 17 bytes to imprecise DECIMAL data types, and limit FLOAT to 8 bytes only.
    But maybe it's due to limitations of floating-point calculations in contemporary processors.

  • Sergiy - Monday, June 25, 2018 6:35 PM

    May be awesome, but still adding no value to the product.
    It's may appear useful only to those who believe in false advertising of security issues around xp_cmdshell.
    Sadly, the array of those believers includes MS developers themselves.

    They'd better spend their effort on introducing "BIG FLOAT" data type.
    It's a shame they allow 17 bytes to imprecise DECIMAL data types, and limit FLOAT to 8 bytes only.
    But maybe it's due to limitations of floating-point calculations in contemporary processors.

    You'll get no argument from me on the merits of xp_CmdShell and the misinformation about it being a security risk.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sergiy - Monday, June 25, 2018 6:35 PM

    Jeff Moden - Monday, June 25, 2018 8:08 AM

    ThomasRushton - Monday, June 25, 2018 7:20 AM

    I know this was a followup to an old SQL2008 question, but, if you're running SQL 2017, check out the new DMV `sys.dm_os_enumerate_filesystem`

    eg:

    SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Office','*')

    It's an awesome tool.  I wish they'd back port some of this stuff to 2016.  I also wish they'd add a BULK EXPORT, support for true CSV and TSV files, and some file handling.

    May be awesome, but still adding no value to the product.
    It's may appear useful only to those who believe in false advertising of security issues around xp_cmdshell.
    Sadly, the array of those believers includes MS developers themselves.

    They'd better spend their effort on introducing "BIG FLOAT" data type.
    It's a shame they allow 17 bytes to imprecise DECIMAL data types, and limit FLOAT to 8 bytes only.
    But maybe it's due to limitations of floating-point calculations in contemporary processors.

    Floating point math is non-trivial for sizes beyond 64-bits in a 64-bit processor.  Decimal math on the other hand, not so much.

    EDIT:  I do think we'll eventually get 128 bit processors, but the amount of design time to double up from 64-bit to 128-bit is going to be large because the design complexity is orders of magnitude larger.

    2ND EDIT:  see here:  https://en.wikipedia.org/wiki/128-bit
      - specifically, the following text:  "While there are currently no mainstream general-purpose processors built to operate on 128-bit integers or addresses, a number of processors do have specialized ways to operate on 128-bit chunks of data. The IBM System/370 could be considered the first simple 128-bit computer, as it used 128-bit floating-point registers. Most modern CPUs feature single-instruction multiple-data (SIMD) instruction sets (Streaming SIMD ExtensionsAltiVec etc.) where 128-bit vector registers are used to store several smaller numbers, such as four 32-bit floating-point numbers. A single instruction can then operate on all these values in parallel. However, these processors do not operate on individual numbers that are 128 binary digits in length; only their registers have the size of 128 bits."

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • Jeff Moden - Thursday, January 21, 2010 10:33 PM

    Without xp_CmdShell... EXEC Master.dbo.xp_DirTree 'yourpathhere',1,1You can use INSERT/EXEC to capture the info in a table if you prebuild the table.

    I am able to successfully retrieve the folder entries using xp_DirTree as long as it is for a locally assigned share but when I reference a location that isn't local to the server it returns no values.  I then used BULK INSERT to read one of the files in the non-local folder I am able to read the file in.  What could be preventing me from getting the directory listing?  I can locate the desired folder using Explorer from the server.

    Example:  
           EXEC Master.dbo.xp_DirTree '\\fs01\it dept$\SQL\Files\',1,1                            - Returns no data
           BULK INSERT #TempLoad    FROM '\\fs01\it dept$\SQL\Files\TestLoad.txt' ...  - Successfully loads contents of text file to temp table

  • These approaches work for me...

    First, xp_cmdshell will launch a cmd.exe process, so if we want to use Powershell, we have to launch Powershell as a sub-process within cmd.exe, and pass in the Powershell code we want to run.  The console output will be captured and written to our table.

    Second, as far as I know, xp_cmdshell will not parse our output in any way, so we need a way to reliably parse the results.  CSV or XML spring to mind.

    Here is the Powershell code I used:

    -- table variable to hold our results
    DECLARE @t TABLE (
    line VARCHAR(1000)
    )

    -- I found it easier to create this long command string in pieces
    -- This also makes it easier to comment out sections while debugging
    DECLARE @cmd VARCHAR(1000) = ''
    SET @cmd += 'powershell -NoProfile -NonInteractive -Command '
    SET @cmd += '"'
    SET @cmd += 'Get-ChildItem C:\Windows\System32'
    SET @cmd += ' | Where-Object {-not $_.PSIsContainer}'
    SET @cmd += ' | Select-Object -Property Fullname, Length, @{Name="""LastWriteTime"""; Expression={$_.LastWriteTime.ToString("""yyyy-MM-dd HH:mm:ss""")}}'
    SET @cmd += ' | ConvertTo-Csv -NoTypeInformation'
    SET @cmd += ' | Select-Object -skip 1'
    SET @cmd += ' | Foreach-Object {$_ -replace ''""'', ''''}'
    SET @cmd += '"'

    -- For debugging. To test this, paste this into a CMD window (NOT Powershell).
    PRINT @cmd

    INSERT INTO @t
    (
    line
    ) EXECUTE xp_cmdshell @cmd

    SELECT * FROM @t

    Explanation of the Powershell code:

    1. Launch powershell with the -NoProfile and -NonInteractive command line arguments.  I found it ran faster with these command line arguments.
    2. Also use the -Command argument to specify the command(s) to run.  I tried using a script block { } but couldn't get that to work.
    3. Get-ChildItem <directory path> gets the files we want.
    4. Where-Object {-not $_.PSIsContainer} filters out directories.
    5. Select-Object -Property Fullname, Length, LastWriteTime selects the properties we wish to return to SQL Server.
    6. @{Name="""LastWriteTime"""; Expression={$_.LastWriteTime.ToString("""yyyy-MM-dd HH:mm:ss""")}} gives me control over the date formatting for LastWriteTime, regardless of the current Culture setting.  And yes you need the triple """ because the command string is quoted with double quotes.  I tried single quotes and couldn't get it to work.
    7. ConvertTo-Csv -NoTypeInformation converts the properties to a CSV formatted string, with annoying double-quotes around all the elements.
    8. Select-Object -skip 1 skips the first header line in the CSV output
    9. Foreach-Object {$_ -replace ''""'', ''''}' replaces the annoying double-quotes in the CSV output with nulls (i.e. removes the double quotes)

    If there is a simpler way to do this please post a reply.

    That's all well and good, but we want to parse the results.

    I don't take credit for the below approach.  See http://jahaines.blogspot.com/2009/06/converting-delimited-string-of-values.html.

    ;
    WITH split_char (line,items)
    AS (
    SELECT line
    ,CONVERT(XML,
    '<Line><Item>'
    + REPLACE(line,',','</Item><Item>')
    + '</Item></Line>') AS items
    FROM @t
    WHERE line IS NOT NULL
    )

    SELECT
    line
    ,items.value('/Line[1]/Item[1]','VARCHAR(256)') AS Fullname
    ,items.value('/Line[1]/Item[2]','BIGINT') AS Filesize
    ,items.value('/Line[1]/Item[3]','DATETIME2(0)') AS LastWriteTime
    FROM split_char

    I personally like Powershell, even with the verbosity of the above command.  However, if you want to use cmd.exe processing, here is an alternative approach:

    INSERT INTO @t
    (
    line
    ) EXECUTE xp_cmdshell 'for %I in (C:\Windows\System32\*.*) do @echo %~fI,%~zI,%~tI'


    ;
    WITH split_char (line,items)
    AS (
    SELECT line
    ,CONVERT(XML,
    '<Line><Item>'
    + REPLACE(line,',','</Item><Item>')
    + '</Item></Line>') AS items
    FROM @t
    WHERE line IS NOT NULL
    )

    SELECT
    line
    ,items.value('/Line[1]/Item[1]','VARCHAR(256)') AS Fullname
    ,items.value('/Line[1]/Item[2]','BIGINT') AS Filesize
    --,items.value('/Line[1]/Item[3]','DATETIME2(0)') AS LastWriteTime
    FROM split_char

    However, I'm not aware of an easy way to cast '22/08/2013 09:21 PM' into a DATETIME2 column.  And even if I did, I would be subject to the Regional Settings of the end user's machine.

    Returning to the Powershell example, it also can return XML output:

    DECLARE @t TABLE (
    line VARCHAR(1000)
    )

    DECLARE @cmd VARCHAR(1000) = ''
    SET @cmd += 'powershell -NoProfile -NonInteractive -Command '
    SET @cmd += '"'
    SET @cmd += 'Get-ChildItem C:\Windows'
    SET @cmd += ' | Where-Object {-not $_.PSIsContainer}'
    SET @cmd += ' | Select-Object -Property Fullname, Length, @{Name="""LastWriteTime"""; Expression={$_.LastWriteTime.ToString("""yyyy-MM-dd HH:mm:ss""")}}'
    SET @cmd += ' | ConvertTo-Xml -NoTypeInformation -As string'
    SET @cmd += '"'

    -- For debugging. Paste this into a CMD window (NOT Powershell) to test.
    PRINT @cmd

    INSERT INTO @t
    (
    line
    ) EXECUTE xp_cmdshell @cmd

    SELECT *
    FROM @t
    WHERE line IS NOT NULL

    This is where my skill set runs out.  I tried this:

    DECLARE @s XML
    SELECT @s=STUFF((SELECT N',' + LTRIM(line) FROM @t FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,1,N'')
    SELECT @s

    Which converts the separate lines from xp_cmdshell into an XML string, but I don't know how to convert that to a table of Fullname, Length, LastWriteTime.

    I think the XML approach might(?) be the way to go once someone posts how to do the conversion.

    I'm on SQL Server 2012, so JSON isn't an option (just replace ConvertTo-Xml with ConvertTo-Json).

     

  • Does anyone know how to read the Powershell XML output into a table of columns?

  • Hi,

    My command is  :  EXEC Master.dbo.xp_DirTree 'D:\Backups\',1,1

    And I have one file in the directory, but the out is  : (0 rows affected)

    I am running this on SQL Server 2017 version.

    Any thoughts please?

  • Tomys wrote:

    Hi,

    My command is  :  EXEC Master.dbo.xp_DirTree 'D:\Backups\',1,1

    And I have one file in the directory, but the out is  : (0 rows affected)

    I am running this on SQL Server 2017 version.

    Any thoughts please?

    Yes... Which machine is the "D:" drive you're trying to interrogate located and from which machine are you running the command?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Tomys wrote:

    Hi,My command is  :  EXEC Master.dbo.xp_DirTree 'D:\Backups\',1,1And I have one file in the directory, but the out is  : (0 rows affected)I am running this on SQL Server 2017 version.Any thoughts please?

    1. providing you haven't mapped the drive, you could do something like EXEC XP_CMDSHELL 'net use Y: \\YourRemoteServer\YourSharedFolder'

    2. If I am not mistaken, a year ago or so someone also has shown how to call to a network folder without mapping it. Perhaps anyone can remind here how, please?

  • Finally it turns out that permissions to the folder were required, permissions to the SQL server service account. Once this was granted

    EXEC Master.dbo.xp_DirTree 'D:\Backups\',1,1 ==> Command worked !

    Thanks all for the inputs

Viewing 13 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply