We were working on a requirement where we needed to use the result of xp_cmdshell in a T-SQL query.
Note: The users running this commands are members of the sysadmin fixed server.If you want others to be able to execute this command, you will have to explicitly grant other users permission to execute this stored procedure
Here’s how to do store the results of xp_cmdshell for later use. The query shown below returns a directory listing of the current directory and stores it in a Temp table to be used later:
CREATE TABLE #Tmp
INSERT INTO #Tmp
EXEC xp_cmdshell 'dir *.exe'
SELECT * FROM #Tmp
--DROP TABLE #Tmp
You can now use a bunch of SUBSTRING commands and extract the date, filesize and filename results, as you want.