Determine number of files in a folder

Question

Post reply

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'?

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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