Query to find size of each disk file in a location

Question

Post reply

Query to find size of each disk file in a location

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
May 8, 2015 at 2:49 pm

#317495

Comments posted to this topic are about the item Query to find size of each disk file in a location
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

You seem to have an endless supply of good scripts. Thanks for another one.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

I dont think that I have endless dupply. I keep writing the scripts(taking help from internet n experts) and apply them in my day to day to avoid doing any kind of manual dba tasks...

Thanks.

Ed Klein-385786 Ten Centuries Points: 1213 More actions · Answer 3

Thanks for the script

Here's a minor enhancement

-- split data into size and filename

SELECT ROW_NUMBER() OVER ( ORDER BY Files DESC ) AS Row

, LEFT(Files, PATINDEX('% %', Files)) AS Size_Bytes --File size in bytes

, RIGHT(Files, LEN(Files) - PATINDEX('% %', Files)) AS FileName

, CAST(REPLACE(LEFT(Files, PATINDEX('% %', Files)), ',', '') AS BIGINT) / 1024 AS FileSize_KB --File size in KB

, CAST(REPLACE(LEFT(Files, PATINDEX('% %', Files)), ',', '') AS BIGINT)/ 1024 / 1024 AS FileSize_MB --File size in MB

FROM #tempList

WHERE LEFT(Files, PATINDEX('% %', Files)) LIKE '%,%';

This prevents integer overflows for gigabit sized files.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

SQL_Hunt

SSC-Dedicated

Points: 33518

May 27, 2015 at 8:58 am

#1800970

Thnx.. I will review n modify

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

I found that you added CASTING with BIGINT. Thanks.

The revised script is here:-

********************************************************************

set nocount on

CREATE TABLE #tempList (Files VARCHAR(500))

DECLARE @result int,@cmd sysname,@cmd3 sysname

DECLARE @var sysname = 'C:\Users\'

SET @cmd3 = 'DIR ' + @var

INSERT INTO #tempList

EXEC MASTER..XP_CMDSHELL @cmd3

--delete all directories

DELETE #tempList WHERE Files LIKE '%<dir>%'

--delete all informational messages

DELETE #tempList WHERE Files LIKE ' %'

--delete the null values

DELETE #tempList WHERE Files IS NULL

--get rid of dateinfo

UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))

--get rid of leading & Trailingspaces

UPDATE #tempList SET files = RTRIM(LTRIM(files))

-- split data into size and filename

SELECT ROW_NUMBER() OVER ( ORDER BY Files DESC ) AS Row

, LEFT(Files, PATINDEX('% %', Files)) AS Size_Bytes --File size in bytes

, RIGHT(Files, LEN(Files) - PATINDEX('% %', Files)) AS FileName

, CAST(REPLACE(LEFT(Files, PATINDEX('% %', Files)), ',', '') AS BIGINT) / 1024 AS FileSize_KB --File size in KB

, CAST(REPLACE(LEFT(Files, PATINDEX('% %', Files)), ',', '') AS BIGINT)/ 1024 / 1024 AS FileSize_MB --File size in MB

FROM #tempList

WHERE LEFT(Files, PATINDEX('% %', Files)) LIKE '%,%';

DRop table #tempList

set nocount off

Thanks.