Find the File Size, last modified date

Use the below procedure to find the required information from file.


CREATE PROCEDURE List_File_Info
AS
SET NOCOUNT ON

DECLARE @SQL VARCHAR(500), @FName VARCHAR(40), @Error INT
DECLARE @Msg VARCHAR(100), @Return INT
DECLARE @FileDate VARCHAR(20), @FileSize VARCHAR(10)

SET DATEFORMAT MDY

IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL DROP TABLE #DirList

CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))

-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\*.*'

SET @Error = @@ERROR
IF @Error <> 0
BEGIN
SET @Msg = 'Got error while getting the File Names with DIR '
GOTO On_Error
END

-- Remove the garbage
DELETE #dirlist WHERE SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR FName IS NULL

--SELECT * FROM #DirList
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName, SUBSTRING(FName,1,20) AS
FileDate, SUBSTRING(FName,30,10) AS FileSize
FROM #dirlist WHERE FName NOT LIKE '%<DIR>%'

OPEN curDir

FETCH NEXT FROM curDir INTO @Fname,@FileDate,@FileSize
WHILE (@@fetch_status = 0)
BEGIN
Print @FName + ' ' + @FileDate + ' ' + @FileSize
FETCH NEXT FROM curDir INTO @Fname,@FileDate,@FileSize
END

CLOSE curDir
DEALLOCATE curDir

DROP TABLE #DirList
DROP TABLE #Errors

RETURN @Error

On_Error:
BEGIN
IF @Error <> 0
BEGIN
SELECT @Msg + '. Error # ' + CAST(@Error AS VARCHAR(10))
RAISERROR(@Msg,12,1)
RETURN @Error
END
END
GO