There are many ways to find the filesize.
This post is all about identifing the filesize using T-SQL,Instantiating OLE Objects and PowerShell. The requirement is to retrieve the size of the specified file.
The return value is going to a floating point value. The details and script are given below
Download the code:- https://gallery.technet.microsoft.com/Determine-size-of-the-file-72f48e3a
T-SQL:
SQL
Edit|Remove
declare @line varchar(255) declare @path varchar(255) declare @command varchar(255) create table #output (line varchar(255)) set @path = 'C:\Appleton Papers - 23 SQL DM - dani 6.28.13.pdf' set @command = 'dir "' + @path +'"' insert into #output exec master.dbo.xp_cmdshell @command select @line = ltrim(replace(substring(line, charindex(')', line)+1,len(line)), ',', '')) from #output where line like '%File(s)%bytes' print cast(replace(@line,'bytes','') as float)/1024 drop table #output
PowerShell:-
PowerShell
Edit|Remove
Function filesize { param([String] $path) try { if(Test-Path $path) { $size=(Get-Item $path).length/1024 write-host "$path size is $size KB" } } catch [System.Exception] { write-host "File not found" } } PS P:\> filesize -path "C:\Appleton Papers - 23 SQL DM - dani 6.28.13.pdf"
Instantiate OLE Objects
You can refer the below link for more information
http://msdn.microsoft.com/en-us/library/ms175079.aspx
PowerShell
Edit|Remove
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO DECLARE @OLEResult INT DECLARE @FileID INT DECLARE @FS INT DECLARE @Size BIGINT -- Create an instance of the file system object EXEC @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT EXEC @OLEResult = sp_OAMethod @FS, 'GetFile', @FileID OUT, 'C:\Appleton Papers - 23 SQL DM - dani 6.28.13.pdf' EXEC @OLEResult = sp_OAGetProperty @FileID, 'Size', @Size OUT print @Size/1024.00
