SQL script to find the latest file in the folder

With the below given script one would be able to find out the latest modified file of specific type (extension)
in a directory

To achieve this, XP_CMDSHELL is used to list the files from the directory. Then extension of the file is provided
in a select statement to retrieve the file which has latest modified timestamp on it

DECLARE @TAB TABLE
(
 Name VARCHAR(100),
 Minimum INT,
 Maximum INT,
 ConfigValue INT,
 RunValue INT
)

DECLARE @File TABLE
(
 ID INT IDENTITY(1,1),
 DATA VARCHAR(4000)
)

DECLARE @Config INT,
  @NewValue INT = 0,
  @DirPath VARCHAR(100),
  @Query NVARCHAR(1000) 

SET @Query = 'EXEC XP_CMDSHELL ''DIR "'+ @DirPath +'" /O-D'''

INSERT INTO @TAB EXEC sp_CONFIGURE

SELECT @Config = ConfigValue FROM @TAB WHERE Name = 'XP_CMDSHELL'

IF(@Config = 0)
BEGIN

  EXEC sp_configure 'XP_CMDSHELL',1 
 RECONFIGURE WITH OVERRIDE 

  SET @NewValue = 1

END


INSERT INTO @File EXEC sp_EXECUTESQL @Query

IF(@NewValue = 1)BEGIN

EXEC sp_configure 'XP_CMDSHELL',0RECONFIGURE WITH OVERRIDE
END

SELECT
 TOP 1 LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(DATA),1,CHARINDEX(' ',REVERSE(DATA)))))) AS Files 
FROM @File 
WHERE DATA LIKE '%.TXT' 
ORDER BY ID