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.

/*********************************************************/
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) !=
and SUBSTRING(Results, 36, Len(Results)) not like (' .%')

Drop Table #MYFILES
DROP Table #MyTAble

END
/*********************************************************/

No comments:

Post a Comment