Check File Size

This script displays the size of a given file.

>>Script Language and Platform: SQL Server
This script displays the size of a given file.

--Usage:
select dbo.udf_CheckFileSize (''d:\sqldumps\testlatency_20041206_08_00_07.TRN'')
select dbo.udf_CheckFileSize (''d:\sqldumps\msdbx.bak'')
select dbo.udf_CheckFileSize (''x:\sqldumpsmsd.bak'')

--Results
145920 bytes
23092736 bytes
-1 means File not found or path not found 
-2 error during FSO process

Author: MAK

Create function udf_CheckFileSize (@filename varchar(1000))
returns bigint
as
--Created by : MAK
--Date: Dec 5, 2004
--Objective: To display the size of the given file
BEGIN
DECLARE @FS int
DECLARE  @OLEResult int
DECLARE  @FileID int
DECLARE @Size bigint
DECLARE @Flag bigint
set @size =0
set @Flag =0
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
EXECUTE @OLEResult = sp_OAMethod @FS, 'GetFile', @FileID OUT,@filename
IF @OLEResult <> 0  
begin
set @Flag =-1
end
else
begin
EXECUTE @OLEResult = sp_OAGetProperty @FileId,'Size', @Size OUT
IF @OLEResult <> 0  
begin
set @Flag =-2
end
end

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
if @flag <> -1 and @flag <> -2 
begin 
set @flag = @size
end
return @flag
END

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home