How can I get file information using SQL Server?

A. Using the undocumented xp_getfiledetails procedure :-

create table #info ( 
alt_namevarchar(255) null, 
size_in_bytesint null, 
creation_date int null,
creation_timeint null,
last_written_dateint null,
last_written_timeint null,
last_accessed_dateint null,
last_accessed_timeint null,
attributesint null
)

insert #info exec master..xp_getfiledetails 'C:DirectoryFile.nam'