Counting numbers of files in a directory.

• Sonnie

  Old Hand

  Points: 370

  More actions

  January 20, 2005 at 2:11 pm

  #163165

  I have 7 set of files like in c:\:

  abc01,abc02,abc03,abc04

  bbb01,bbb02,bbb03

  Now I can use :

  EXECUTE master.dbo.xp_dirtree N'C:\', 1, 1

  to get a total of 7 files in my sql.

  How do I isolate the abc* files to know I have 4?

  Any help

  Thanks

  GoodSon

• Osoba

  SSCommitted

  Points: 1964

  More actions

  January 20, 2005 at 2:52 pm

  #538405

  Would this work for what you need?

  create table #temp(

  [name] varchar(100),

  depth int,

  [file] int)

  insert into #temp

  EXECUTE master.dbo.xp_dirtree N'C:\', 1, 1

  select count([name]) from #temp where [file]=1 and [name] like 'abc%'

  drop table #temp

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  January 20, 2005 at 3:38 pm

  #538413

  While this will work, note that xp_dirtree is undocumented.

  You can achieve the same result, if you pipe the result from a simple xp_cmdshell DIR... into a table like so:

  create table #temp(

  [name] varchar(100))

  DECLARE @sql varchar(100)

  SET @sql = 'DIR C:\ /b/a-d-s'

  INSERT INTO #temp EXEC master.dbo.xp_cmdshell @sql

  SELECT COUNT(*) FROM #temp WHERE name LIKE 'abc%'

  DROP TABLE #temp

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Sonnie

  Old Hand

  Points: 370

  More actions

  January 21, 2005 at 3:22 pm

  #538616

  I appreciate all the replies. It worked following Osoba example. You are all great. Thanks