Binaryworld - File details using NT commands & undocumented SP. ... [ SQL Server -> T-SQL ]

Getting file details is easy using the undocumented extended SP 'xp_getfiledetails'. But you can't rely on this to be the same between versions of SQL Server or even service packs. So here is an alternative method using the standard NT commands. The undocumented SP is also shown for completeness.

Click here to copy the following block

declare @justname varchar( 30 ) ,
    @filename varchar( 30 ) ,
    @cmdstr varchar( 255 )
select @filename = '%systemroot%\explorer.exe'
select @justname = right( @filename , charindex('\' , reverse( @filename) ) - 1 )
select @cmdstr = 'for /F "tokens=1,2,3,4" %i in (''dir ' +
         @filename + '^|findstr /I "' +
         @justname + '"'') do @echo ~%l~%i~%j~%k~'
select @cmdstr AS "NT Command"
-- The generated NT cmd string
/*
NT Command
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
for /F "tokens=1,2,3,4" %i in ('dir %systemroot%\explorer.exe^|findstr /I "explorer.exe"') do @echo ~%l~%i~%j~%k~
*/
exec master..xp_cmdshell @cmdstr
-- The tilda delimited file details:
/*
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
~explorer.exe~12/07/1999~12:00p~238,352~
*/

-- Undocumented Method
select @filename = 'c:\winnt\explorer.exe'
exec master..xp_getfiledetails @filename
-- SP output:
/*
Alternate Name Size Creation Date Creation Time Last Written Date Last Written Time Last Accessed Date Last Accessed Time Attributes
-------------- ------ ------------- ------------- ----------------- ----------------- ------------------ ------------------ -----------
NULL      238352 19991207   120000    19991207     120000      20000904      0         32
*/