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.

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 27, 2015 at 8:17 am

  #1800943

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

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  May 27, 2015 at 8:20 am

  #1800945

  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

  May 27, 2015 at 8:56 am

  #1800967

  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

  May 27, 2015 at 8:58 am

  #1800970

  Thnx.. I will review n modify

  Thanks.

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  May 28, 2015 at 2:19 pm

  #1801402

  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.