Function to get the file information from server.

  SQL Advanced

To use this function you need to give your server user sys.spOACreate permissions.

 

Code:

-- =============================================
-- Author: Bruno Figueiredo
-- Description: Returns the file information
-- =============================================
CREATE FUNCTION fn_getfiledetails 
(
@Filename sysname
)
/*
How to use: select * from fn_getfiledetails('c:\file.ext')
*/
RETURNS 
@filedetails TABLE 
(
[Path] VARCHAR(100),
[ShortPath] VARCHAR(100),
[Type] VARCHAR(100),
[DateCreated] datetime,
[DateLastAccessed] datetime,
[DateLastModified] datetime,
[Attributes] INT,
[size] INT
)

AS
BEGIN
DECLARE 
@hr INT,
@objFileSystem INT, 
@objFile INT, 
@Path VARCHAR(100), 
@ShortPath VARCHAR(100),
@Type VARCHAR(100),
@DateCreated datetime,
@DateLastAccessed datetime,
@DateLastModified datetime,
@Attributes INT,
@size INT

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT

IF @hr=0 EXEC @hr = sp_OAMethod @objFileSystem, 'GetFile', @objFile out,@Filename

IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile, 'Path', @path OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile, 'ShortPath', @ShortPath OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile, 'Type', @Type OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile, 'DateCreated', @DateCreated OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile, 'DateLastAccessed', @DateLastAccessed OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile, 'DateLastModified', @DateLastModified OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile, 'Attributes', @Attributes OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile, 'size', @size OUT

EXEC sp_OADestroy @objFileSystem
EXEC sp_OADestroy @objFile

INSERT @filedetails
SELECT [Path]= @Path,
[ShortPath]= @ShortPath,
[Type]= @Type,
[DateCreated]= @DateCreated ,
[DateLastAccessed]= @DateLastAccessed,
[DateLastModified]= @DateLastModified,
[Attributes]= @Attributes,
[size]= @size
RETURN
END