Find All Files In Specific Folder Using T- SQL

As I am doing lot of automation, recent requirement comes to me where I have to take action on all files present on specific folder as per there creation date. So I created this procedure which gives me all files and its creation date from sql query analyzer. And once I have all files and its creation date we can take any action as per requirement.

QUICK DOWNLOAD

/*********************************************************/

CREATE PROCEDURE FILES_IN_DRIVES (@PATH varchar (4000))

AS

BEGIN

/*************************************

Created By: Saurabh Sinha

CREATED DATE:  10/07/2014

Description: This will give all files available in folder

Syntax: EXEC FILES_IN_DRIVES 'path of directory'

*************************************/

DECLARE @CMD varchar(8000)

CREATE Table #MyTable (Results varchar(500))

CREATE Table #MYFILES (create_date datetime ,is_directory int ,[Name]  varchar (500))

SET @cmd = 'dir '  + @path + ' /A /OS /-C'

INSERT INTO #MyTAble

EXEC XP_CMDSHELL @cmd

SELECT LEFT(Results, 20) [create_date],

SUBSTRING(Results, 36, Len(Results)) AS [name]FROM #MyTable

WHERE ISNULL(PATINDEX('%__/__/____%', Results), 0) != 0 

and SUBSTRING(Results, 36, Len(Results)) not like (' .%')

Drop Table #MYFILES

DROP Table #MyTAble

END

/*********************************************************/