Determine number of files in a folder

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  October 16, 2013 at 8:05 am

  #278972

  DECLARE @path varchar(500)

  SET @path = 'C:\MyFolder\MyFile.txt'

  DECLARE @result INT

  EXEC master.dbo.xp_fileexist @path, @result OUTPUT

  SELECT @result

  The code above enables me to determine if a file exists.

  From within a stored procedure, can you count the number of files in 'MyFolder'?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 16, 2013 at 8:46 am

  #1658959

  How about using dir?

  (untested code)

  DECLARE @cmd nvarchar(500)

  SET @cmd = 'dir C:\MyFolder\'

  CREATE TABLE #DirOutput(

  files varchar(500))

  INSERT INTO #DirOutput

  EXEC master.dbo.xp_cmdshell @cmd

  SELECT *

  FROM #DirOutput

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  October 16, 2013 at 9:30 am

  #1658978

  Thanks for your reply. I tried it ... C:\MyFolder has one file in it.

  The temp table ended up with one column called 'Files' with 11 rows in it showing ...

  Volume in drive C is OS

  Volume Serial Number is 1234-1ABC

  null

  Directory of C:\MyFolder

  null

  and so on ...

  One of the rows contains '1 File(s) 11 bytes'

  but I just want how many files are in that directory and nothing else.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 16, 2013 at 9:44 am

  #1658985

  I guess you're lacking of imagination. You just needed to adjust the temp table query.

  DECLARE @cmd nvarchar(500)

  SET @cmd = 'dir C:\'

  CREATE TABLE #DirOutput(

  files varchar(500))

  INSERT INTO #DirOutput

  EXEC master.dbo.xp_cmdshell @cmd

  SELECT CAST( LEFT( files, PATINDEX('%File(s)%', files) - 1) AS int)

  FROM #DirOutput

  WHERE files LIKE '%File(s)%'

  DROP TABLE #DirOutput

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 16, 2013 at 9:48 am

  #1658986

  Just another option using dir parameters.

  DECLARE @cmd nvarchar(500)

  SET @cmd = 'dir C:\ /A:A'

  CREATE TABLE #DirOutput(

  files varchar(500))

  INSERT INTO #DirOutput

  EXEC master.dbo.xp_cmdshell @cmd

  SELECT COUNT(*)

  FROM #DirOutput

  WHERE files LIKE '[0-9][0-9]/%'

  DROP TABLE #DirOutput

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  October 16, 2013 at 10:12 am

  #1658992

  Thanks again for your help. I found this in the mean time which does the job too.

  IF OBJECT_ID('#DirTree') IS NOT NULL

  DROP TABLE #DirTree

  CREATE TABLE #DirTree (

  SubDirectory nvarchar(255),

  Depth smallint,

  FileFlag bit

  )

  INSERT INTO #DirTree (SubDirectory, Depth, FileFlag)

  EXEC xp_dirtree 'C:\MyFolder', 1, 1

  SELECT COUNT(*) FROM #DirTree

  IF OBJECT_ID('#DirTree') IS NOT NULL

  DROP TABLE #DirTree