Technical Article

Stored procedure to expose information for a file

,

I wanted to be able to use info about an operating system file in TSQL. So I wrote this stored procedure that uses the special stored procedures xp_fileexist and xp_getfiledetails.

This can tell if a file or folder exists, and if it is a file what is its size, created date and time, last written date and time, last accessed date and time as well as file attributes e.g. Read Only or Compressed.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FileInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FileInfo]
GO

CREATE PROCEDURE dbo.sp_FileInfo
(  @FileName  nvarchar(2048) = NULL
 , @FileExists  nvarchar(1)  = NULL OUTPUT 
 , @IsDir nvarchar(1)  = NULL OUTPUT
 , @ParentDirExists nvarchar(1)  = NULL OUTPUT
 , @AltName nvarchar(20) = NULL OUTPUT
 , @FileSize nvarchar(11) = NULL OUTPUT
 , @CrDate nvarchar(8)  = NULL OUTPUT
 , @CrTime nvarchar(6)  = NULL OUTPUT
 , @LastWriteDatenvarchar(8)  = NULL OUTPUT
 , @LastWriteTimenvarchar(6)  = NULL OUTPUT
 , @LastAccessDatenvarchar(8)  = NULL OUTPUT
 , @LastAccessTimenvarchar(6)  = NULL OUTPUT
 , @Attributesnvarchar(11) = NULL OUTPUT )

AS 

/********1*********2*********3*********4*********5*********6*********8*********9*********0*********1*********2*****
**
**  $Archive$
**  $Revision$
**  $Author$ 
**  $Modtime$
**
*******************************************************************************************************************
**
**  $Log$
**
*******************************************************************************************************************
**
**Name: sp_FileInfo
**Desc: This procedure checks for information for given file or directory
**It returns the values as separate parameters.
**NOTE: Attributes can be transalated as follows:
**
**Attribute FileFolder
**Read Only R117i.e. add 16 to make reference to folder
**HiddenH218
**RH319
**ArchiveA3248
**AR3349
**AH3450
**ARH3551
**none12838
**Compressed C20482064Note:  all attribute values are additive
**
**
**Return values: 0 = Successful, error number if failed
**              
*******************************************************************************************************************
**Change History - All Author comments below this point.
*******************************************************************************************************************
**  AuthorDateDescription
**  ----------------------------------------------------------
**  NBJ30-Jan-2002Original - SP to get results of xp_fileexist and xp_getfiledetails
******************************************************************************************************************/
Declare   @Err int
Select @Err = 0

If @FileName is Null
Begin
Return 1
End

Set nocount on

Create table #tmp1
(
 FileExists nvarchar(1)
,IsDir nvarchar(1)
,ParentDirExists nvarchar(1)
)
Create table #tmp2
(
 AltName nvarchar(20)
,FileSize nvarchar(11)
,CrDate nvarchar(8)
,CrTime nvarchar(6)
,LastWriteDate nvarchar(8)
,LastWriteTime nvarchar(6)
,LastAccessDate nvarchar(8)
,LastAccessTime nvarchar(6)
,Attributes nvarchar(11)
)
Select @Err = @@Error
If @Err <> 0 
Begin
Return @Err
End
Insert into #tmp1
Exec Master.dbo.xp_fileexist @FileName
Select @Err = @@Error
If @Err <> 0 
Begin
Return @Err
End
Insert Into #tmp2
Exec Master.dbo.xp_getfiledetails @FileName
Select @Err = @@Error
If @Err <> 0 
Begin
Return @Err
End
Select 
 @FileExists   = FileExists
,@IsDir   = IsDir
,@ParentDirExists = ParentDirExists
From #tmp1 Where FileExists In ('0','1')
Select @Err = @@Error
If @Err <> 0 
Begin
Return @Err
End
Select 
  @FileSize =  FileSize
, @CrDate =  CrDate
, @CrTime =  CrTime
, @LastWriteDate=  LastWriteDate
, @LastWriteTime=  LastWriteTime
, @LastAccessDate=  LastAccessDate
, @LastAccessTime=  LastAccessTime
, @Attributes=  Attributes 
, @AltName =  AltName
From #tmp2 Where cast(Attributes as int) > 0 -- used to pick up row with values

Select @Err = @@Error
If @Err <> 0 
Begin
Return @Err
End
--  Use this to check values
/*
Select   @FileExists  
 , @IsDir 
 , @ParentDirExists 
 , @AltName 
 , @FileSize 
 , @CrDate 
 , @CrTime 
 , @LastWriteDate
 , @LastWriteTime
 , @LastAccessDate
 , @LastAccessTime
 , @Attributes
*/drop table #tmp1
drop table #tmp2

Select @Err = @@Error
If @Err <> 0 
Begin
Return @Err
End

Return 0



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate