How to find the date created and date modified values of all files in a location.

  • Hello Experts

    Can anyone please tell me a good script to find the details of creation date and modified date of all files located in a path please?

    I wanna write few custom messages before I delete some files from a path.

    Please suggest.

    Thanks.

  • This powershell script will do that:-

    $filepath = "C:\Temp"

    gci $filepath | select-object name, lastwritetime, creationtime

    Is that what you needed?

  • If you need to do some logic you could use:-

    $filepath = "C:\Temp"

    $date = "2015/06/06 03:50:00"

    $files = gci $filepath | select-object name, lastwritetime, creationtime

    foreach($file in $files)

    {

    $fileCreateDate = $file.creationtime

    if($fileCreateDate -gt $date)

    {

    write-host "Do Something"

    }

    else

    {

    Write-Host "Do Something Else"

    }

    }

  • Thanks.

    Anyway you can help me getting the script in xp_cmdshell?

    Thanks.

  • I'm honestly not sure how to do that with xp_cmdshell.

    What are you trying to do?

  • SQL-DBA-01 (6/12/2015)


    Thanks.

    Anyway you can help me getting the script in xp_cmdshell?

    Were the two methods of mine that you found on a couple of the other posts too slow (and I agree they might have been)? If so, do a search for "Listing files using PowerShell". You can call PowerShell from xp_CmdShell if you want.

    I do have a script that works for just one directory and wasn't built to be recursive. If that'll suit you, let me know.

    --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.


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

  • I'm not sure if you have explored this option or if it would be any better of what you've already got.

    IF OBJECT_ID('tempdb..#Files') IS NOT NULL DROP TABLE #Files;

    CREATE TABLE #Files( output nvarchar(255));

    IF OBJECT_ID('tempdb..#FilesCreateDate') IS NOT NULL DROP TABLE #FilesCreateDate;

    CREATE TABLE #FilesCreateDate( output nvarchar(255));

    IF OBJECT_ID('tempdb..#FilesModDate') IS NOT NULL DROP TABLE #FilesModDate;

    CREATE TABLE #FilesModDate( output nvarchar(255));

    INSERT INTO #Files

    EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /B'

    INSERT INTO #FilesCreateDate

    EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /TC'

    INSERT INTO #FilesModDate

    EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /TW'

    SELECT f.output AS [filename],

    REPLACE( LEFT( cd.output, 19), ' ', ' ') + '.m.' AS CreateDate,

    REPLACE( LEFT( md.output, 19), ' ', ' ') + '.m.' AS ModificationDate

    FROM #Files f

    JOIN #FilesCreateDate cd ON f.output = SUBSTRING( cd.output, CHARINDEX(f.output, cd.output), LEN(f.output))

    JOIN #FilesModDate md ON f.output = SUBSTRING( md.output, CHARINDEX(f.output, md.output), LEN(f.output));

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Everyone, Jeff,Luis, & DBACold.

    I will have further look per your suggestions.

    Let me tell what was the requirement and till what point I did.

    Guys were not intersted to use reporting services where I could easily hand some of the reports for their visibility and then they could extract the results in any format (csv/excel/pdf) they want.

    Insrtead, I was asked to automate a solution to extract the results to each .txt "good" readable format, The output file should be suffixed with datetime,hh_mm_ss so that we can retain them forany no of days per rwquirement. Moreover, guys can change the script also any number of times per the rewquirement, but the job should run daily and send the output to a location from where they can copy the output for reference and check.

    Now, i have done entirely using T sQL and created job which is handling all the above matter.

    What I thought is to post them before I delete any files that how many .txt are available before today's date which all can be deleted based on logic. and also this rwequirement will keep changing, so I will keep adding stuf to my TSQL.

    Now, I thought to change Tsql to procedure, so that it can create a compiled plan but there are restrictions creating procs, so I'm good with Tsql

    Thanks.

  • Do the files actually have a date and time in the file name? That would make life pretty easy. If so, please post the "general" format of the file names. If not, then understood and it can be done. Does it need to be for just one directory or does it need to be recursive?

    --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.


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

  • Luis Cazares (6/12/2015)


    I'm not sure if you have explored this option or if it would be any better of what you've already got.

    IF OBJECT_ID('tempdb..#Files') IS NOT NULL DROP TABLE #Files;

    CREATE TABLE #Files( output nvarchar(255));

    IF OBJECT_ID('tempdb..#FilesCreateDate') IS NOT NULL DROP TABLE #FilesCreateDate;

    CREATE TABLE #FilesCreateDate( output nvarchar(255));

    IF OBJECT_ID('tempdb..#FilesModDate') IS NOT NULL DROP TABLE #FilesModDate;

    CREATE TABLE #FilesModDate( output nvarchar(255));

    INSERT INTO #Files

    EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /B'

    INSERT INTO #FilesCreateDate

    EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /TC'

    INSERT INTO #FilesModDate

    EXEC xp_cmdshell 'dir C:\Users\Public\Downloads /TW'

    SELECT f.output AS [filename],

    REPLACE( LEFT( cd.output, 19), ' ', ' ') + '.m.' AS CreateDate,

    REPLACE( LEFT( md.output, 19), ' ', ' ') + '.m.' AS ModificationDate

    FROM #Files f

    JOIN #FilesCreateDate cd ON f.output = SUBSTRING( cd.output, CHARINDEX(f.output, cd.output), LEN(f.output))

    JOIN #FilesModDate md ON f.output = SUBSTRING( md.output, CHARINDEX(f.output, md.output), LEN(f.output));

    Luis, I had started to play with the /TC and /TW switches, but I didn't know how to get both columns listed with a single command. Then, I got called away and didn't pick it up again. I'm glad someone else thought of the same thing.

  • Hi Jeff,

    As of now, I m keeping output to s shared location accessible by dev n testers. Req may change in future. No specification of output names, but i m giving it as scriptname_yyyy_mm_dd_hh_mm_ss.

    Wrote a logic to ignore .sql before it generates output with .txt format.

    Hi Luis,

    Your script is v nice. Many thnx.

    Missed to mention in d prior mail

    Great weekend to every1. Cheers..

    Thanks.

Viewing 11 posts - 1 through 11 (of 11 total)

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