List Files with "Date Modified" 2

[JoeZim]

I've been able to use the xp_cmdshell to get a list of file names from a given server directory, using the following:

CREATE TABLE #Files (MyFile varchar(200))

DECLARE @Path varchar(400)

SET @Path = 'D:\Basel\Inquiry\'

EXECUTE sp_ListFiles @Path,'#Files','%.dbf',NULL,0

SELECT * FROM #Files

order by MyFile DESC

DROP TABLE #Files

How can I get the "Date Modified" for each file into a separate column?

I've used the following code to get in the ballpark, but I was hoping to get a cleaner result set (i.e. "File Name" & "Date Modified"):

create table #Backup (Filename varchar(250))

Declare @Filename varchar(250), 
	@RemoveFile varchar(100),
	@Directory varchar(250), 
	@MyDate datetime, 
	@LongFileName varchar(200)

Set @Directory = '"dir D:\Basel\Inquiry"'

Insert into #Backup
exec master..xp_cmdshell @Directory

delete
from #Backup
where Filename is null
GO

select * from #Backup

drop table #Backup

Many thanks to Catadmin and others for providing the useful code I've used thus far.

Regards,
Joe

 

[jbenson001]

You could loop through each row of the table, and check if the first 10 chars are a valid date.. if so then you can get the rest of the info based on position in the row.

something like

if isdate(left(FileName),1)
begin
--use substrings etc to pull out other info here..

end

 

[SQLDenis]

I did something like that here

http://sqlservercode.blogspot.com/2006/01/use-xpcmdshell-to-get-all-file-names.html

I modified the code for your neeeds

CREATE TABLE tempList (Files VARCHAR(500))

INSERT INTO tempList
EXEC MASTER..XP_CMDSHELL 'dir c:\ '


--delete all directories
DELETE tempList WHERE Files LIKE '%<dir>%'

--delete all informational messages
DELETE tempList WHERE Files LIKE ' %'

--delete the null values
DELETE tempList WHERE Files IS NULL

ALTER TABLE tempList add FileDAte varchar(20)
GO
--split dateinfo
UPDATE tempList SET FileDate =LEFT(files,20)

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

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

--split data into size and filename
SELECT FileDAte,LEFT(files,PATINDEX('% %',files)) AS Size,
RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FileName
FROM tempList 

drop table tempList

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

thanks for the code Denis

 

[JoeZim]

Thanks Denis, that will work for me!

 

[SQLDenis]

No problem thats for the starrs

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

>>No problem thats for the starrs

that should have been

No problem thanks for the starrs

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

or

No problem thanks for the star[!] r [/!]s


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

That was on purpose George

1 star = star
2 stars = starrs

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[onpnt]

Question regarding file/directory size. Can you iterate each logical drive found on the server and report the size remaining for each one?

Good post. This has proved very useful as I'm n the process of putting a few monitoring and auditing tasks on our servers. I did start writing an ActiveX script to do most of the work and then using CDONTS send an email upon finding disk space under a percentage all wrapped in a DTS that runs daily. Or do you think that is still the way to go?

Thanks


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[onpnt]

Scratch the question really.

I continued some reading and found

EXECUTE master..xp_fixeddrives

Which returns

drive MB free
----- -----------
C     382
E     8979
F     134155
G     547289
H     78

This this I can maket his task run pretty quick and without interuption to anything.



____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[SQLDenis]

exec master..xp_fixeddrives

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[onpnt]

____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[SQLDenis]

I gues we posted at the same time

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/