Determine size of the file using T-SQL/Powershell/Ole Objects

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

filesize1

 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"

filesize3

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

filesize3

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL, T-SQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s