Sometimes we need to list down file/folder’s information from a folder like filename, extension, created date and modified date etc.
The below query can give you the list of all content’s info from given folder path.
SQL Query:
DECLARE @FolderPath VARCHAR(MAX) = ''
IF OBJECT_ID('Tempdb..#FileDetails') IS NOT NULL
DROP TABLE #FileDetails
CREATE TABLE #FileDetails (FileDetails VARCHAR(MAX))
INSERT INTO #FileDetails (FileDetails)
EXEC ('EXEC XP_CMDSHELL ''powershell.exe -c "Get-ChildItem '''''+@FolderPath+''''' | SELECT Name,CreationTime,LastWriteTime,Extension|foreach{$_.Name+''''@''''+$_.CreationTime+''''|''''+$_.LastWriteTime+''''#''''+$_.Extension}"''')
SELECT SUBSTRING(FileDetails,0,CHARINDEX('@',FileDetails)) AS [File Name]
, CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('@',FileDetails)+1,19)) AS [Creation Date Time]
, CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('|',FileDetails)+1,19)) AS [Modified Date Time]
, ISNULL(NULLIF(SUBSTRING(FileDetails,CHARINDEX('#',FileDetails)+1,LEN(FileDetails)),''),'Directory') AS [Extension]
FROM #FileDetails
WHERE FileDetails IS NOT NULL
Output Content:
| Column Name | Description | Remarks |
| File Name | Name of content | File name, Folder name etc. |
| Creation Date Time | File/Folder creation date-time | |
| Modified Date Time | Last Modified date-time | |
| Extension | File extension | like.. .sql, .xlxs etc. |
Advertisements
