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

Question

Post reply

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

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
June 11, 2015 at 4:24 pm

#301402

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.

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

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

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

This powershell script will do that:-

$filepath = "C:\Temp"

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

Is that what you needed?

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 2

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"

}

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Thanks.

Anyway you can help me getting the script in xp_cmdshell?

Thanks.

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 4

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

What are you trying to do?

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 5

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)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 8

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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 10

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.

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

Cookies on SQLServerCentral