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. |